package com.hongxun.project.assets.exportdetailed;

import com.hongxun.common.exception.BusinessException;
import com.hongxun.framework.config.RuoYiConfig;
import com.hongxun.framework.web.controller.BaseController;
import com.hongxun.framework.web.domain.AjaxResult;
import com.hongxun.project.assets.Tools.ExcelUtils;
import com.hongxun.project.assets.Tools.OutPutUtils;
import com.hongxun.project.assets.bByq.controller.BByqController;
import com.hongxun.project.assets.bByq.domain.BByq;
import com.hongxun.project.assets.bByq.service.IBByqService;
import com.hongxun.project.assets.iBoxes.domain.IBoxes;
import com.hongxun.project.assets.iBoxes.service.IIBoxesService;
import com.hongxun.project.assets.iDevice.domain.IDevice;
import com.hongxun.project.assets.iDevice.service.IIDeviceService;
import com.hongxun.project.assets.iLine.domain.ILine;
import com.hongxun.project.assets.iLine.service.IILineService;
import com.hongxun.project.assets.iMaterial.domain.IMaterial;
import com.hongxun.project.assets.iMaterial.service.IIMaterialService;
import com.hongxun.project.assets.iPicture.domain.IPicture;
import com.hongxun.project.assets.iPicture.service.IIPictureService;
import com.hongxun.project.assets.iPoint.domain.IPoint;
import com.hongxun.project.assets.iPoint.service.IIPointService;
import com.hongxun.project.assets.iPole.domain.IPole;
import com.hongxun.project.assets.iPole.service.IIPoleService;
import com.hongxun.project.assets.kBj.domain.KBj;
import com.hongxun.project.assets.kBj.service.IKBjService;
import com.hongxun.project.assets.kCj.domain.KCj;
import com.hongxun.project.assets.kCj.service.IKCjService;
import com.hongxun.project.assets.kHgq.domain.KHgq;
import com.hongxun.project.assets.kHgq.service.IKHgqService;
import com.hongxun.project.assets.kPdKg.domain.KPdKg;
import com.hongxun.project.assets.kPdKg.service.IKPdKgService;
import com.hongxun.project.assets.pDevice.service.IPDeviceService;
import com.hongxun.project.common.CommonController;
import com.hongxun.project.system.dept.domain.Dept;
import com.hongxun.project.system.dept.service.IDeptService;
import com.hongxun.project.system.user.domain.User;
import com.hongxun.project.system.user.service.IUserService;
import com.lowagie.text.*;
import com.lowagie.text.pdf.BaseFont;
import com.lowagie.text.rtf.RtfWriter2;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Table;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.expression.spel.ast.Selection;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.BindException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;

import static com.hongxun.common.utils.Arith.div;
import static com.hongxun.project.assets.Tools.ExcelUtils.deleteFile;
import static com.hongxun.project.assets.Tools.ExcelUtils.genContextStyle;

/**
 *  导出表格
 *
 * Created by john on 2019/4/19.
 */
@Controller
@RequestMapping("/assets/exportDetiail_Excel")
public class ExportDetiail_Excel extends BaseController {

    private static final Logger log = LoggerFactory.getLogger(CommonController.class);

    private String ROOT = RuoYiConfig.getProfile();

    @Autowired
    private IIPointService iPointService;
    @Autowired
    private IILineService iLineService;
    @Autowired
    private IPDeviceService pDeviceService;
    @Autowired
    private IIDeviceService iDeviceService;
    @Autowired
    private IIMaterialService iMaterialService;
    @Autowired
    private IBByqService bByqService;
    @Autowired
    private IIPictureService iPictureService;
    @Autowired
    private IIBoxesService iBoxesService;
    @Autowired
    private IUserService userService;
    @Autowired
    private IDeptService deptService;
    @Autowired
    private IIPoleService iPoleService;
    @Autowired
    private IKBjService kBjService;
    @Autowired
    private IKHgqService kHgqService;
    @Autowired
    private IKCjService kCjService;
    @Autowired
    private IKPdKgService kPdKgService;




    /**
     * 生成excel并下载--资产表
     */
    @GetMapping("expAssets")
    public void exportExcel(String fileName, String id ,Boolean delete, HttpServletResponse response, HttpServletRequest request) {

        String name = "";
        if(fileName!="" || fileName!=null){
            name = fileName.substring(fileName.lastIndexOf("-")+1,fileName.length());
        }

        File newFile = new ExcelUtils().createNewFile(fileName);
        String filePath = RuoYiConfig.getDownloadPath() + name;
        // File newFile = new File("d:/ss.xls");

        // 新文件写入数据，并下载
//        InputStream is = null;
        OutputStream out = null;
        HSSFWorkbook workbook = null;
        HSSFSheet sheet = null;
        try {
//            is = new FileInputStream(newFile);// 将excel文件转为输入流
//            workbook = new HSSFWorkbook(is);// 创建个workbook，

            ClassPathResource resource = new ClassPathResource("static/temp/zc.xls");
            workbook = new HSSFWorkbook(resource.getInputStream());
            sheet = workbook.getSheetAt(0);//获取sheet

            // 获取第一个sheet
//            sheet = workbook.getSheetAt(0);
        } catch (Exception e1) {
            e1.printStackTrace();
        }

        if (sheet != null) {
            try {
                // 写数据
//                FileOutputStream fos = new FileOutputStream(newFile);
                HSSFRow row = sheet.getRow(4);
                HSSFCellStyle contextStyle = genContextStyle(workbook);
                if (row == null) {
                    row = sheet.createRow(4);
                }
                HSSFCell cell = row.getCell(0);
                if (cell == null) {
                    cell = row.createCell(0);

                }

                // 定义一个list集合假数据
                List<Map<String, Object>> list = extportAssets(id);

                sheet.getRow(0).getCell(0).setCellValue(fileName);


                if(list.size() != 0 ){
                    for (int m = 0; m < list.size(); m++) {                     //  判断行数
                        row = sheet.createRow(m + 4);

                        int io2 = 0;
                        for (int i = 0; i < 1; i++) {          //   判断单元格数
                            if(m==0){
                                cell = row.createCell(i);cell.setCellValue(i); cell.setCellStyle(contextStyle);                                                      //  序号
                                cell = row.createCell(i+1);cell.setCellValue(list.get(m).get("unitName").toString());   cell.setCellStyle(contextStyle);      //  供电所
                                cell = row.createCell(i+2);cell.setCellValue(list.get(m).get("area").toString());       cell.setCellStyle(contextStyle);        //  台区名称
                                cell = row.createCell(i+3);cell.setCellValue(list.get(m).get("lineName").toString());   cell.setCellStyle(contextStyle);      //  所属馈路
                                cell = row.createCell(i+4);cell.setCellValue(list.get(m).get("lingModleJK").toString());cell.setCellStyle(contextStyle);    //  架空线路规格
                                cell = row.createCell(i+5);cell.setCellValue(list.get(m).get("lingJK").toString());     cell.setCellStyle(contextStyle);    //  架空线路数量
                                cell = row.createCell(i+6);cell.setCellValue(list.get(m).get("lingModleDL").toString());cell.setCellStyle(contextStyle);    //  电缆线路规格
                                cell = row.createCell(i+7);cell.setCellValue(list.get(m).get("lingDL").toString());     cell.setCellStyle(contextStyle);    //  电缆线路数量
                                cell = row.createCell(i+8);cell.setCellValue(list.get(m).get("v380").toString());       cell.setCellStyle(contextStyle);    //  380v线路长度
                                cell = row.createCell(i+9);cell.setCellValue(list.get(m).get("v220").toString());       cell.setCellStyle(contextStyle);    //  220v线路长度
                                cell = row.createCell(i+10);cell.setCellValue(list.get(m).get("lineCount").toString()); cell.setCellStyle(contextStyle);     //  线路长度合计
                                cell = row.createCell(i+11);cell.setCellValue(list.get(m).get("poleHeight8").toString()); cell.setCellStyle(contextStyle);   //  8米杆
                                cell = row.createCell(i+12);cell.setCellValue(list.get(m).get("poleHeight10").toString());cell.setCellStyle(contextStyle);  //  10米杆
                                cell = row.createCell(i+13);cell.setCellValue(list.get(m).get("poleHeight12").toString());cell.setCellStyle(contextStyle);  //  12米杆
                                cell = row.createCell(i+14);cell.setCellValue(list.get(m).get("poleHeight12_T").toString());cell.setCellStyle(contextStyle);//  12米特性杆
                                cell = row.createCell(i+15);cell.setCellValue(list.get(m).get("poleHeight15").toString());  cell.setCellStyle(contextStyle);//  15米杆
                                cell = row.createCell(i+16);cell.setCellValue(list.get(m).get("poleHeight15_T").toString());cell.setCellStyle(contextStyle);//  15米特性杆
                                cell = row.createCell(i+17);cell.setCellValue(list.get(m).get("poleHeight18").toString());  cell.setCellStyle(contextStyle);//  18米杆
                                cell = row.createCell(i+18);cell.setCellValue(list.get(m).get("poleHeight18_T").toString());cell.setCellStyle(contextStyle);//  18米特性杆
                                cell = row.createCell(i+19);cell.setCellValue(list.get(m).get("sumTotal").toString());      cell.setCellStyle(contextStyle);//  杆基总和
                                cell = row.createCell(i+20);cell.setCellValue(list.get(m).get("poleShape").toString());     cell.setCellStyle(contextStyle);//  杆塔形状
                                cell = row.createCell(i+21);cell.setCellValue(list.get(m).get("manufacturer").toString());  cell.setCellStyle(contextStyle);//  生产厂家
                                cell = row.createCell(i+22);cell.setCellValue(list.get(m).get("comDate").toString());       cell.setCellStyle(contextStyle);//  投运日期
                                cell = row.createCell(i+23);cell.setCellValue(list.get(m).get("oneSurfaceSmart").toString());cell.setCellStyle(contextStyle);// 单相户表--智能型数量
                                cell = row.createCell(i+24);cell.setCellValue(list.get(m).get("oneBox_one").toString());    cell.setCellStyle(contextStyle);//  单相表箱--1表位
                                cell = row.createCell(i+25);cell.setCellValue(list.get(m).get("oneBox_two").toString());    cell.setCellStyle(contextStyle);//  单相表箱--2表位
                                cell = row.createCell(i+26);cell.setCellValue(list.get(m).get("oneBox_four").toString());   cell.setCellStyle(contextStyle);//  单相表箱--4表位
                                cell = row.createCell(i+27);cell.setCellValue(list.get(m).get("oneBox_six").toString());    cell.setCellStyle(contextStyle);//  单相表箱--6表位
                                cell = row.createCell(i+28);cell.setCellValue(list.get(m).get("oneBox_nine").toString());   cell.setCellStyle(contextStyle);//  单相表箱--9表位
                                cell = row.createCell(i+29);cell.setCellValue(list.get(m).get("oneBox_twelve").toString()); cell.setCellStyle(contextStyle);//  单相表箱--12表位
                                cell = row.createCell(i+30);cell.setCellValue(list.get(m).get("oneBox_total").toString());  cell.setCellStyle(contextStyle);//  单相表箱合计
                                cell = row.createCell(i+31);cell.setCellValue(list.get(m).get("threeSurfaceSmart").toString());cell.setCellStyle(contextStyle);//  三相用户表计--智能型数量
                                cell = row.createCell(i+32);cell.setCellValue(list.get(m).get("threeBox").toString());      cell.setCellStyle(contextStyle);//  三相表箱
                                cell = row.createCell(i+33);cell.setCellValue(list.get(m).get("concentrator").toString());  cell.setCellStyle(contextStyle);//  集中器
                                cell = row.createCell(i+34);cell.setCellValue(list.get(m).get("mutualInductor").toString());cell.setCellStyle(contextStyle);//  互感器
                                cell = row.createCell(i+35);cell.setCellValue(list.get(m).get("lowBox").toString());        cell.setCellStyle(contextStyle);//  低压电缆分支箱
                                cell = row.createCell(i+36);cell.setCellValue(list.get(m).get("lowCabinet").toString());    cell.setCellStyle(contextStyle);//  成套低压配电柜
                                cell = row.createCell(i+37);cell.setCellValue("");                                          cell.setCellStyle(contextStyle);//  备注

                            }else{
//                            cell = row.createCell(4);cell.setCellValue(list.get(m).get("lingModleJK").toString());    cell.setCellStyle(contextStyle);//  架空线路规格
//                            cell = row.createCell(5);cell.setCellValue(list.get(m).get("lingJK").toString());         cell.setCellStyle(contextStyle);//  架空线路数量
//                            cell = row.createCell(6);cell.setCellValue(list.get(m).get("lingModleDL").toString());    cell.setCellStyle(contextStyle);//  电缆线路规格
//                            cell = row.createCell(7);cell.setCellValue(list.get(m).get("lingDL").toString());         cell.setCellStyle(contextStyle);//  电缆线路数量

                                cell = row.createCell(i);cell.setCellValue(i);                   cell.setCellStyle(contextStyle);                                                      //  序号
                                cell = row.createCell(i+1);cell.setCellValue("");       cell.setCellStyle(contextStyle);      //  供电所
                                cell = row.createCell(i+2);cell.setCellValue("");       cell.setCellStyle(contextStyle);        //  台区名称
                                cell = row.createCell(i+3);cell.setCellValue("");       cell.setCellStyle(contextStyle);      //  所属馈路
                                cell = row.createCell(i+4);cell.setCellValue(list.get(m).get("lingModleJK").toString());cell.setCellStyle(contextStyle);    //  架空线路规格
                                cell = row.createCell(i+5);cell.setCellValue(list.get(m).get("lingJK").toString());     cell.setCellStyle(contextStyle);    //  架空线路数量
                                cell = row.createCell(i+6);cell.setCellValue(list.get(m).get("lingModleDL").toString());cell.setCellStyle(contextStyle);    //  电缆线路规格
                                cell = row.createCell(i+7);cell.setCellValue(list.get(m).get("lingDL").toString());     cell.setCellStyle(contextStyle);    //  电缆线路数量
                                cell = row.createCell(i+8);cell.setCellValue("");       cell.setCellStyle(contextStyle);    //  380v线路长度
                                cell = row.createCell(i+9);cell.setCellValue("");       cell.setCellStyle(contextStyle);    //  220v线路长度
                                cell = row.createCell(i+10);cell.setCellValue("");      cell.setCellStyle(contextStyle);     //  线路长度合计
                                cell = row.createCell(i+11);cell.setCellValue("");      cell.setCellStyle(contextStyle);   //  8米杆
                                cell = row.createCell(i+12);cell.setCellValue("");      cell.setCellStyle(contextStyle);  //  10米杆
                                cell = row.createCell(i+13);cell.setCellValue("");      cell.setCellStyle(contextStyle);  //  12米杆
                                cell = row.createCell(i+14);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  12米特性杆
                                cell = row.createCell(i+15);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  15米杆
                                cell = row.createCell(i+16);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  15米特性杆
                                cell = row.createCell(i+17);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  18米杆
                                cell = row.createCell(i+18);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  18米特性杆
                                cell = row.createCell(i+19);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  杆基总和
                                cell = row.createCell(i+20);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  杆塔形状
                                cell = row.createCell(i+21);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  生产厂家
                                cell = row.createCell(i+22);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  投运日期
                                cell = row.createCell(i+23);cell.setCellValue("");      cell.setCellStyle(contextStyle);// 单相户表--智能型数量
                                cell = row.createCell(i+24);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  单相表箱--1表位
                                cell = row.createCell(i+25);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  单相表箱--2表位
                                cell = row.createCell(i+26);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  单相表箱--4表位
                                cell = row.createCell(i+27);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  单相表箱--6表位
                                cell = row.createCell(i+28);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  单相表箱--9表位
                                cell = row.createCell(i+29);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  单相表箱--12表位
                                cell = row.createCell(i+30);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  单相表箱合计
                                cell = row.createCell(i+31);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  三相用户表计--智能型数量
                                cell = row.createCell(i+32);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  三相表箱
                                cell = row.createCell(i+33);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  集中器
                                cell = row.createCell(i+34);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  互感器
                                cell = row.createCell(i+35);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  低压电缆分支箱
                                cell = row.createCell(i+36);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  成套低压配电柜
                                cell = row.createCell(i+37);cell.setCellValue("");      cell.setCellStyle(contextStyle);//  备注

                            }
                        }

                    }

                    addRowCells(sheet , list , contextStyle);  //  合并单元格
                }

                FileOutputStream fos = new FileOutputStream(newFile);
                workbook.write(fos);
                fos.flush();
                fos.close();
                // 下载
                InputStream fis = new BufferedInputStream(new FileInputStream(
                        newFile));
                byte[] buffer = new byte[fis.available()];
                fis.read(buffer);
                fis.close();
                response.reset();
                response.setContentType("text/html;charset=UTF-8");
                out = new BufferedOutputStream(
                        response.getOutputStream());
                response.setContentType("application/x-msdownload");
                String newName = URLEncoder.encode(
                        fileName+"-" + System.currentTimeMillis() + ".xls",
                        "UTF-8");
                response.addHeader("Content-Disposition",
                        "attachment;filename=\"" + newName + "\"");
                response.addHeader("Content-Length", "" + newFile.length());
                out.write(buffer);
                out.flush();
            } catch (Exception e) {
                log.error("导出表格错误");
                throw new BusinessException("导出Excel失败,请联系管理员");
//                e.printStackTrace();
            } finally {
                try {
                    if (null != out) {
                        out.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        // 删除创建的新文件
//         deleteFile(newFile);
    }

    /**
     * 删除行
     *
     * @param sheet
     * @param rowIndex
     */
    public static void removeRow(HSSFSheet sheet, int rowIndex) {
        int lastRowNum=sheet.getLastRowNum();
        if(rowIndex>=0&&rowIndex<lastRowNum)
            sheet.shiftRows(rowIndex+1,lastRowNum,-1);//将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
        if(rowIndex==lastRowNum){
            HSSFRow removingRow=sheet.getRow(rowIndex);
            if(removingRow!=null)
                sheet.removeRow(removingRow);
        }
    }

    /**
     *  合并单元格：删除单元格并上移
     *
     * @param sheet
     * @param list
     */
    public static void addRowCells(HSSFSheet sheet, List<Map<String, Object>> list , HSSFCellStyle contextStyle) {

        int shop = list.get(0).size();
        System.err.println("shop=="+shop);

        int ints = list.size();
        System.err.println("list长度=="+ints);

        int shrn = sheet.getLastRowNum();   //  最后一行
        System.err.println("shrn=="+shrn);

        int oneCell = 0 , twoCell = 0;
        //  赋值
        for (int i = 0; i <= shop; i++) {
            if (i == 4 || i == 5 || i == 6 || i ==7) {

                for (int j = 0; j < shrn-3; j++) {
                    for (int k = 0; k < shrn-3-j; k++) {
                        //  第j行4列，5列的值是否为空
                        String cv0 = sheet.getRow(4+j).getCell(4).getStringCellValue();
                        String cv1 = sheet.getRow(4+j).getCell(5).getStringCellValue();
                        String cv00 = sheet.getRow(4+j).getCell(6).getStringCellValue();
                        String cv11 = sheet.getRow(4+j).getCell(7).getStringCellValue();
                        //  第j+1行4列，5列的值是否为空
//                        if(shrn-3 >3 && j < shrn-4){
                        if(j < shrn-4){
                            String cv2 = sheet.getRow(4+j+1).getCell(4).getStringCellValue();
                            String cv3 = sheet.getRow(4+j+1).getCell(5).getStringCellValue();
                            String cv22 = sheet.getRow(4+j+1).getCell(6).getStringCellValue();
                            String cv33 = sheet.getRow(4+j+1).getCell(7).getStringCellValue();
                            //  如果第二列的单元格值为空，则将上下两行单元格合并，并重新赋值
                            if(cv0 == "" && cv1 == ""){
                                if(cv2 != "" && cv3 != ""){
                                    sheet.getRow(4+j).getCell(4).setCellValue(cv2);
                                    sheet.getRow(4+j).getCell(5).setCellValue(cv3);

                                    sheet.getRow(4+j+1).getCell(4).setCellValue("");
                                    sheet.getRow(4+j+1).getCell(5).setCellValue("");

                                    ++oneCell;
                                }
                            }
                            if(cv00 == "" && cv11 == ""){
                                if(cv22 != "" && cv33 != ""){
                                    sheet.getRow(4+j).getCell(6).setCellValue(cv22);
                                    sheet.getRow(4+j).getCell(7).setCellValue(cv33);

                                    sheet.getRow(4+j+1).getCell(6).setCellValue("");
                                    sheet.getRow(4+j+1).getCell(7).setCellValue("");

                                    ++twoCell;
                                }
                            }
                        }
                    }
                }

            }
        }

        System.err.println("oneCell=="+oneCell);
        System.err.println("twoCell=="+twoCell);

        //  删除行
        int sd = 0;
        for (int i = 4; i <= shrn; i++) {
            String cv0 = "" ,  cv1 = "" ,  cv00 = "" ,  cv11 = "";

            if( (sheet.getRow(i).getCell(4) != null) &&
                    (sheet.getRow(i).getCell(5) != null) &&
                    (sheet.getRow(i).getCell(6) != null) &&
                    (sheet.getRow(i).getCell(7) != null)){
                cv0 = sheet.getRow(i).getCell(4).getStringCellValue();
                cv1 = sheet.getRow(i).getCell(5).getStringCellValue();
                cv00 = sheet.getRow(i).getCell(6).getStringCellValue();
                cv11 = sheet.getRow(i).getCell(7).getStringCellValue();
            }else{
                cv0 = "" ;cv1 = "" ;cv00 = "" ;cv11 = "" ;
            }

//                String cv0 = sheet.getRow(i).getCell(4).getStringCellValue();
//                String cv1 = sheet.getRow(i).getCell(5).getStringCellValue();
//                String cv00 = sheet.getRow(i).getCell(6).getStringCellValue();
//                String cv11 = sheet.getRow(i).getCell(7).getStringCellValue();

                if(cv0 == "" && cv1=="" && cv00 =="" && cv11==""){
                    removeRow(sheet,i-sd);
                    ++sd;
                }

        }
        //  合并单元格
        int swing = sheet.getLastRowNum();   //  最后一行
        System.err.println("swing=="+swing);

        for (int i = 0; i <= shop; i++) {
            if (i == 4 || i == 5 || i == 6 || i ==7) {
                continue;
            }else{
                sheet.addMergedRegion(new CellRangeAddress(4, swing, i, i));
            }
        }




        //  删除单元格并合并
        /*for (int i = 0; i <= shop; i++) {
            if (i == 4 || i == 5 || i == 6 || i ==7) {
                continue;
            }else{
                if(oneCell > twoCell) {
                    sheet.addMergedRegion(new CellRangeAddress(4, shrn - Math.abs(4 - oneCell), i, i));
                    for (int j = 0; j < Math.abs(4 - oneCell); j++) {
                        removeRow(sheet,shrn-j);
                    }
                }
                else if(oneCell < twoCell) {
                    sheet.addMergedRegion(new CellRangeAddress(4, shrn - Math.abs(4 - twoCell), i, i));
                    for (int j = 0; j < Math.abs(4 - twoCell); j++) {
                        removeRow(sheet,shrn-j);
                    }
                }

            }

        }*/

    }



    /**
     * 生成excel并下载--台账表
     */
    @GetMapping("expLineAssets")
    public void exportLineAssetsExcel(String fileName, String id ,Boolean delete, HttpServletResponse response, HttpServletRequest request) {

        File newFile = new ExcelUtils().createNewFile(fileName);
        String name = "";
        if(fileName!="" || fileName!=null){
            name = fileName.substring(fileName.lastIndexOf("-")+1,fileName.length());
        }
        String filePath = RuoYiConfig.getDownloadPath() + name;
        // 新文件写入数据，并下载
        InputStream is = null;
        OutputStream out = null;
        HSSFWorkbook workbook = null;
        HSSFSheet sheet = null;

        try {
//            is = new FileInputStream(newFile);// 将excel文件转为输入流
//            workbook = new HSSFWorkbook(is);// 创建个workbook，
            ClassPathResource resource = new ClassPathResource("static/temp/tz.xls");
            workbook = new HSSFWorkbook(resource.getInputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }

        //  ANTSHEET：第几个sheet页 ； ANTROW：第几行开始写入数据
        HSSFSheet sheet0 = getSheet(workbook , sheet , newFile , 0 , 2);
        System.err.println("sheet0=="+sheet0);
        HSSFSheet sheet1 = getSheet(workbook , sheet , newFile , 1 , 3);
        HSSFSheet sheet2 = getSheet(workbook , sheet , newFile , 2 , 3);
        HSSFSheet sheet3 = getSheet(workbook , sheet , newFile , 3 , 3);
            try {
                String nameos = fileName.substring(0,fileName.indexOf("-"));

                System.err.println("nameos=="+nameos);
                if(sheet0!=null)
                sheet0.getRow(0).getCell(0).setCellValue(nameos+"-台账表");
                if(sheet1!=null)
                sheet1.getRow(0).getCell(0).setCellValue(nameos+"-杆位明细表");
                if(sheet2!=null)
                sheet2.getRow(0).getCell(0).setCellValue(nameos+"-表箱明细表");
                if(sheet3!=null)
                sheet3.getRow(0).getCell(0).setCellValue(nameos+"-表计明细表");
                // 写数据
//                FileOutputStream fos = new FileOutputStream(newFile);
                HSSFRow row = null;
                getTZdoc(workbook , row , sheet0 , id); //  台账表
                getGTdoc(workbook , row , sheet1 , id); //  杆位明细表
                getBXdoc(workbook , row , sheet2 , id); //  表箱明细表
                getBJdoc(workbook , row , sheet3 , id); //  表计明细表
                FileOutputStream fos = new FileOutputStream(newFile);
                workbook.write(fos);
                fos.flush();
                fos.close();
                // 下载
                InputStream fis = new BufferedInputStream(new FileInputStream(
                        newFile));
                byte[] buffer = new byte[fis.available()];
                fis.read(buffer);
                fis.close();
                response.reset();
                response.setContentType("text/html;charset=UTF-8");
                out = new BufferedOutputStream(response.getOutputStream());
                response.setContentType("application/x-msdownload");
                String newName = URLEncoder.encode(
                        fileName+"-" + System.currentTimeMillis() + ".xls","UTF-8");
                response.addHeader("Content-Disposition",
                        "attachment;filename=\"" + newName + "\"");
                response.addHeader("Content-Length", "" + newFile.length());
                out.write(buffer);
                out.flush();
            } catch (Exception e) {
                log.error("导出表格错误");
                throw new BusinessException("导出Excel失败,请联系管理员");
//                e.printStackTrace();
            } finally {
                try {
                    if (null != out) {
                        out.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        // 删除创建的新文件
        deleteFile(newFile);
    }


    /**
     * 生成excel并下载--变压器表
     */
    @GetMapping("expByqMB")
    public void expByqMB(HttpServletResponse response, HttpServletRequest request) {
        String fileName = "变压器";
        File newFile = new ExcelUtils().createNewFile(fileName);

//        String name = "";
//        if(fileName!="" || fileName!=null){
//            name = fileName.substring(fileName.lastIndexOf("-")+1,fileName.length());
//        }
//        String filePath = RuoYiConfig.getDownloadPath() + fileName;
        User user = userService.selectUserById(getUserId());
        Dept dept = new Dept();
        if(user!=null)
            dept = deptService.selectDeptById(user.getDeptId());


        // 新文件写入数据，并下载
//        InputStream is = null;
        OutputStream out = null;
        HSSFWorkbook workbook = null;
        HSSFSheet sheet = null;

        try {
//            is = new FileInputStream(newFile);// 将excel文件转为输入流
//            workbook = new HSSFWorkbook(is);// 创建个workbook，

            ClassPathResource resource = new ClassPathResource("static/temp/byq.xls");
            workbook = new HSSFWorkbook(resource.getInputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }

        //  ANTSHEET：第几个sheet页 ； ANTROW：第几行开始写入数据
        HSSFSheet sheet0 = getSheet(workbook , sheet , newFile , 0 , 2);

            try {
                // 写数据
//                FileOutputStream fos = new FileOutputStream(newFile);
                HSSFRow row = null;
                sheet0.getRow(0).getCell(0).setCellValue((dept.getDeptName()==null? "":dept.getDeptName())+"变压器统计");
                getByqDoc(workbook , row , sheet0); //  变压器表
                FileOutputStream fos = new FileOutputStream(newFile);
                workbook.write(fos);
                fos.flush();
                fos.close();
                // 下载
                InputStream fis = new BufferedInputStream(new FileInputStream(
                        newFile));
                byte[] buffer = new byte[fis.available()];
                fis.read(buffer);
                fis.close();
                response.reset();
                response.setContentType("text/html;charset=UTF-8");
                out = new BufferedOutputStream(response.getOutputStream());
                response.setContentType("application/x-msdownload");
                String newName = URLEncoder.encode(
                        dept.getDeptName()+"变压器统计-" + System.currentTimeMillis() + ".xls",
                        "UTF-8");
                response.addHeader("Content-Disposition",
                        "attachment;filename=\"" + newName + "\"");
                response.addHeader("Content-Length", "" + newFile.length());
                out.write(buffer);
                out.flush();
            } catch (Exception e) {
                log.error("导出表格错误");
                throw new BusinessException("导出Excel失败,请联系管理员");
//                e.printStackTrace();
            } finally {
                try {
                    if (null != out) {
                        out.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        // 删除创建的新文件
        deleteFile(newFile);
    }

    /**
     *  台账表第一个sheet页
     *
     */
    public HSSFSheet getSheet(HSSFWorkbook workbook ,HSSFSheet sheet , File newFile , int ANTSHEET , int ANTROW){
        try {
            // 获取第一个sheet
            sheet = workbook.getSheetAt(ANTSHEET);
            HSSFRow row = sheet.getRow(ANTROW);
            if (row == null) {
                row = sheet.createRow(ANTROW);
            }
        } catch (Exception e1) {
            e1.printStackTrace();
        }

        return sheet;
    }

    /**
     *  所有变压器
     *
     * @param workbook
     * @param row
     * @param sheet
     */
    public void getByqDoc(HSSFWorkbook workbook , HSSFRow row , HSSFSheet sheet){

        if (row == null) {
            row = sheet.createRow(2);
        }
        HSSFCellStyle contextStyle = genContextStyle(workbook);
        HSSFCell cell ;

        //  获取当前登录用户所属变电所的所有变压器
        List<BByq> bByqList = new ArrayList<>();
        BByq bByq = new BByq();
        if(getUserId()!=1){
            User user = userService.selectUserById(getUserId());
            bByq.setDeptId(user.getDeptId().toString());
            bByqList = bByqService.selectBByqList(bByq);
        }else{
            bByqList = bByqService.selectBByqList(bByq);
        }

        for (int i = 0; i < bByqList.size(); i++) {
            row = sheet.createRow(i+2);
            //  表数
            IPoint iPoint = new IPoint();
            iPoint.setByqId(bByqList.get(i).getId());
            List<IPoint> iPoints = iPointService.selectIPointList(iPoint);
            int tots = 0;
            for (IPoint point : iPoints) {
                IBoxes iBoxes = new IBoxes();
                iBoxes.setPId(point.getId());
                List<IBoxes> iBoxess = iBoxesService.selectIBoxesList(iBoxes);
                if(iBoxess.size() > 0){
                    for (IBoxes boxess : iBoxess) {
                        if(boxess.getInstallCount()!=null){
                            tots += boxess.getInstallCount();
                        }

                    }

                }
            }


            /*IDevice iDevice = new IDevice();
            iDevice.setByqId(bByqList.get(i).getId());
            List<IDevice> iDeviceList = iDeviceService.selectIDeviceList(iDevice);
            int ios = 0 ;
            for (int j = 0; j < iDeviceList.size(); j++) {
                if(iDeviceList.get(j).getBigType().equals("电能表")){
                    ++ios;
                }
            }*/

            row.setHeightInPoints(25);  // 行高
            cell = row.createCell(0);cell.setCellValue(bByqList.get(i).getName());cell.setCellStyle(contextStyle);      //  变压器名称
            cell = row.createCell(1);
            if(bByqList.get(i).getVolume() == null){
                cell.setCellValue("");
            }else{
                cell.setCellValue(bByqList.get(i).getVolume());
            }
            cell.setCellStyle(contextStyle);  //  变压器容量

            cell = row.createCell(2);
            if(bByqList.get(i).getModel() == null)
                cell.setCellValue("");
            else
                cell.setCellValue(bByqList.get(i).getModel());
            cell.setCellStyle(contextStyle);  //  变压器规格型号

            cell = row.createCell(3);cell.setCellValue(tots);cell.setCellStyle(contextStyle);  //  表数

        }

    }


    public void getTZdoc(HSSFWorkbook workbook , HSSFRow row , HSSFSheet sheet , String id){
        if (row == null) {
            row = sheet.createRow(2);
        }
        HSSFCellStyle contextStyle = genContextStyle(workbook);
        HSSFCell cell ;
//        if (cell == null) {
//            cell = row.createCell(0);
//        }
        //  表格字段：设备名称，单位，数量，设备型号，生产厂家，出厂年月，投运日期，运维部门，运维班组，出厂编号，备注

        //  变压器
        BByq bByq = new BByq();
        bByq.setId(id);
        List<BByq> bByqList = bByqService.selectBByqList(bByq);
        for (int i = 0; i < bByqList.size(); i++) {
            row.setHeightInPoints(25);  // 行高
            cell = row.createCell(i);cell.setCellValue(bByqList.get(i).getName());cell.setCellStyle(contextStyle);  //  设备名称
            cell = row.createCell(i+1);cell.setCellValue("台");cell.setCellStyle(contextStyle);            //  单位
            cell = row.createCell(i+2);cell.setCellValue(1);cell.setCellStyle(contextStyle);               //  数量
            cell = row.createCell(i+3);
            if (bByqList.get(i).getFactory() == null)
                cell.setCellValue("");	//	生产厂家
            else
                cell.setCellValue(bByqList.get(i).getFactory());	//	生产厂家
            cell.setCellStyle(contextStyle);               //  生产厂家

            cell = row.createCell(i+4);
            if (bByqList.get(i).getModel() == null)
                cell.setCellValue("");	//	设备型号
            else
                cell.setCellValue(bByqList.get(i).getModel());	//	设备型号
            cell.setCellStyle(contextStyle);//  设备型号

            cell = row.createCell(i+5);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂年月
            cell = row.createCell(i+6);
            if (bByqList.get(i).getRunDate() ==null)
                cell.setCellValue("");	//	投运日期
            else
                cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(bByqList.get(i).getRunDate()));	//	投运日期
            cell.setCellStyle(contextStyle);//  设备型号

            cell = row.createCell(i+7);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维部门
            cell = row.createCell(i+8);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维保管组
            cell = row.createCell(i+9);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂编号
            cell = row.createCell(i+10);cell.setCellValue("");cell.setCellStyle(contextStyle);//  备注
        }

        //  设备
        IDevice iDevice = new IDevice();
        iDevice.setByqId(id);
//            iDevice.setLineId(line.getId());
        List<IDevice> iDeviceList = iDeviceService.selectIDeviceList(iDevice);
        List<IDevice> list = new ArrayList<>();
        if(iDeviceList != null){
            for (IDevice device : iDeviceList) {
                if (device.getBigType().equals("低压配电柜/盘")
                        || device.getMidType().equals("集中器")
                        || device.getBigType().equals("低压电流互感器")
                        || device.getBigType().equals("分支箱")){
                    list.add(device);
                }
            }
        }
        //  采集器--型号
        KCj kCj = new KCj();
        List<KCj> kCjList = new ArrayList<>();
        for (IDevice device : list) {
            if (device.getMidType().equals("集中器")) {
                kCj.setByqId(device.getByqId());
                kCj.setDeviceId(device.getId());
                kCjList = kCjService.selectKCjList(kCj);
            }
        }


        //  低压电流互感器--型号
        KHgq kHgq = new KHgq();
        List<KHgq> kHgqList = new ArrayList<>();
        for (IDevice device : list) {
            if (device.getBigType().equals("低压电流互感器")) {
                kHgq.setByqId(device.getByqId());
                kHgq.setDeviceId(device.getId());
                kHgqList = kHgqService.selectKHgqList(kHgq);
            }
        }

        //  低压配电柜--型号
        KPdKg kPdKg = new KPdKg();
        kPdKg.setByqId(id);
        List<KPdKg> kPdKgList = new ArrayList<>();
        StringBuffer stringBuffer = new StringBuffer();
        kPdKgList = kPdKgService.selectKPdKgList(kPdKg);
        String opq = "";
        if(kPdKgList.size()!=0){
            for (KPdKg pdKg : kPdKgList) {
                stringBuffer.append(pdKg.getCode());
                stringBuffer.append(";");
            }
            opq = stringBuffer.substring(0,stringBuffer.lastIndexOf(";"));
        }

        for (int i = 0; i < list.size(); i++) {

            row=sheet.createRow(i+3);//创建第三行
            row.setHeightInPoints(25);  // 行高

            cell = row.createCell(0);cell.setCellValue(list.get(i).getBigType());cell.setCellStyle(contextStyle);  //  设备名称
            cell = row.createCell(1);cell.setCellValue("台");cell.setCellStyle(contextStyle);            //  单位
            cell = row.createCell(2);cell.setCellValue(1);cell.setCellStyle(contextStyle);               //  数量
            cell = row.createCell(3);cell.setCellValue("");cell.setCellStyle(contextStyle);	            //	生产厂家

            cell = row.createCell(4);
            if (list.get(i).getName() == null)
                cell.setCellValue("");	//	设备型号
            else
                cell.setCellValue(list.get(i).getName());	//	设备型号
            cell.setCellStyle(contextStyle);//  设备型号

            cell = row.createCell(5);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂年月
            cell = row.createCell(6);cell.setCellValue("");cell.setCellStyle(contextStyle);//  投运日期
            cell = row.createCell(7);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维部门
            cell = row.createCell(8);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维保管组

            if(list.get(i).getBigType().equals("低压电流互感器")){
                String ac = kHgqList.get(0).getACode();
                String bc = kHgqList.get(0).getBCode();
                String cc = kHgqList.get(0).getCCode();
                cell = row.createCell(9);cell.setCellValue(ac+","+bc+","+cc);cell.setCellStyle(contextStyle);//  出厂编号
            }else if(list.get(i).getMidType().equals("集中器")){
                String code = kCjList.get(0).getCode();
                cell = row.createCell(9);cell.setCellValue(code);cell.setCellStyle(contextStyle);//  出厂编号
            }else if(list.get(i).getBigType().equals("低压配电柜/盘")){
                cell = row.createCell(9);cell.setCellValue(opq);cell.setCellStyle(contextStyle);//  出厂编号
            }else{
                cell = row.createCell(9);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂编号
            }

            cell = row.createCell(10);cell.setCellValue("");cell.setCellStyle(contextStyle);//  备注
        }

        int asd = list.size();  //  设备条数

        //  关口表
        KBj kBj = new KBj();
        kBj.setByqId(id);
        List<KBj> kBjList = kBjService.selectKBjList(kBj);
        List<KBj> kBjList11 = new ArrayList<>();
        List<IDevice> iDeviceList1 = new ArrayList<>();

        for (KBj bj : kBjList) {
            if (bj.getType() != null && bj.getType().equals("关口表")){
                kBjList11.add(bj);
            }
        }

//        int bsc = kBjList11.size();
        for (int i = 0; i < kBjList11.size(); i++) {

//            if(kBjList.get(i).getType()!=null && kBjList.get(i).getType().equals("关口表")){
//                ++bsc;
            IDevice iDevice1 = iDeviceService.selectIDeviceById(kBjList11.get(i).getBoxId());

                if(asd==0){
                    row=sheet.createRow(i+3);
                }else{
                    row=sheet.createRow(i+3+asd);
                }


                /*else if(asd==1){
                    row=sheet.createRow(i+4);
                }else if(asd==2){
                    row=sheet.createRow(i+5);
                }else if(asd==3){
                    row=sheet.createRow(i+6);
                }else if(asd==4){
                    row=sheet.createRow(i+7);
                }else if(asd==5){
                    row=sheet.createRow(i+8);
                }*/

                row.setHeightInPoints(25);  // 行高

                cell = row.createCell(0);cell.setCellValue("关口表");cell.setCellStyle(contextStyle);  //  设备名称
                cell = row.createCell(1);cell.setCellValue("只");cell.setCellStyle(contextStyle);            //  单位
                cell = row.createCell(2);cell.setCellValue(1);cell.setCellStyle(contextStyle);               //  数量
                cell = row.createCell(3);cell.setCellValue("");cell.setCellStyle(contextStyle);	            //	生产厂家

                cell = row.createCell(4);cell.setCellValue(iDevice1.getName());cell.setCellStyle(contextStyle);//  设备型号

                cell = row.createCell(5);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂年月
                cell = row.createCell(6);cell.setCellValue("");cell.setCellStyle(contextStyle);//  投运日期
                cell = row.createCell(7);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维部门
                cell = row.createCell(8);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维保管组

                cell = row.createCell(9);
                if (kBjList11.get(i).getBCode() == null)
                    cell.setCellValue("");	//	出厂编号
                else
                    cell.setCellValue(kBjList11.get(i).getBCode());	//	出厂编号
                cell.setCellStyle(contextStyle);//  出厂编号


                cell = row.createCell(10);cell.setCellValue("");cell.setCellStyle(contextStyle);//  备注
//            }

        }
        int bsc = kBjList11.size();     //  关口表数量

        //  三相表箱、表计，单相表箱、表计
        Integer iop = iBoxesService.getCountsForType(id,"单相" , null);
        String ios = iBoxesService.getCountsListForNum(id,"单相");
//        int ios = kBjService.getCountForPid(id,"单相");
        for (int i = 0; i < 2; i++) {

            if(i==0){
                if(bsc == 0){
                    row=sheet.createRow(i+3+asd);
                }else{
                    row=sheet.createRow(i+4+asd);
                }

                cell = row.createCell(0);cell.setCellValue("单相表箱");cell.setCellStyle(contextStyle);  //  设备名称
                cell = row.createCell(1);cell.setCellValue("只");cell.setCellStyle(contextStyle);            //  单位
                cell = row.createCell(2);
                if(iop !=null) cell.setCellValue(iop); else cell.setCellValue("0");
                cell.setCellStyle(contextStyle);               //  数量
            }else{
                if(bsc == 0){
                    row=sheet.createRow(i+3+asd);
                }else{
                    row=sheet.createRow(i+4+asd);
                }
                cell = row.createCell(0);cell.setCellValue("单相表计");cell.setCellStyle(contextStyle);  //  设备名称
                cell = row.createCell(1);cell.setCellValue("只");cell.setCellStyle(contextStyle);            //  单位
                cell = row.createCell(2);
                if(ios !=null) cell.setCellValue(ios); else cell.setCellValue("0");
                cell.setCellStyle(contextStyle);               //  数量
            }
            row.setHeightInPoints(25);  // 行高

            cell = row.createCell(3);cell.setCellValue("");cell.setCellStyle(contextStyle);	            //	生产厂家
            cell = row.createCell(4);cell.setCellValue("");cell.setCellStyle(contextStyle);//  设备型号
            cell = row.createCell(5);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂年月
            cell = row.createCell(6);cell.setCellValue("");cell.setCellStyle(contextStyle);//  投运日期
            cell = row.createCell(7);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维部门
            cell = row.createCell(8);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维保管组
            cell = row.createCell(9);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂编号
            cell = row.createCell(10);cell.setCellValue("");cell.setCellStyle(contextStyle);//  备注
        }
        Integer ioa = iBoxesService.getCountsForType(id,"三相",null);
        String iod = iBoxesService.getCountsListForNum(id,"三相");
//        int iod = kBjService.getCountForPid(id,"三相");
        for (int i = 0; i < 2; i++) {

            if(i==0){
                if(bsc == 0){
                    row=sheet.createRow(i+5+asd);
                }else{
                    row=sheet.createRow(i+6+asd);
                }
                cell = row.createCell(0);cell.setCellValue("三相表箱");cell.setCellStyle(contextStyle);  //  设备名称
                cell = row.createCell(1);cell.setCellValue("只");cell.setCellStyle(contextStyle);            //  单位
                cell = row.createCell(2);
                if(ioa !=null) cell.setCellValue(ioa); else cell.setCellValue("0");
                cell.setCellStyle(contextStyle);               //  数量
            }else{
                if(bsc == 0){
                    row=sheet.createRow(i+5+asd);
                }else{
                    row=sheet.createRow(i+6+asd);
                }
                cell = row.createCell(0);cell.setCellValue("三相表计");cell.setCellStyle(contextStyle);  //  设备名称
                cell = row.createCell(1);cell.setCellValue("只");cell.setCellStyle(contextStyle);            //  单位
                cell = row.createCell(2);
                if(iod !=null) cell.setCellValue(iod); else cell.setCellValue("0");
                cell.setCellStyle(contextStyle);               //  数量
            }
            row.setHeightInPoints(25);  // 行高
            cell = row.createCell(3);cell.setCellValue("");cell.setCellStyle(contextStyle);	            //	生产厂家
            cell = row.createCell(4);cell.setCellValue("");cell.setCellStyle(contextStyle);//  设备型号
            cell = row.createCell(5);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂年月
            cell = row.createCell(6);cell.setCellValue("");cell.setCellStyle(contextStyle);//  投运日期
            cell = row.createCell(7);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维部门
            cell = row.createCell(8);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维保管组
            cell = row.createCell(9);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂编号
            cell = row.createCell(10);cell.setCellValue("");cell.setCellStyle(contextStyle);//  备注
        }

        //  电杆
        IPoint iPoint11 = new IPoint();
        iPoint11.setByqId(id);
        iPoint11.setType("杆塔");
        List<IPoint> iPointList11 = iPointService.selectIPointList(iPoint11);
        for (int i = 0; i < 1; i++) {
            if(i==0){
                if(bsc == 0){
                    row=sheet.createRow(i+7+asd);
                }else{
                    row=sheet.createRow(bsc+8+asd);
                }
            }
            row.setHeightInPoints(25);  // 行高

            cell = row.createCell(0);cell.setCellValue("电杆");cell.setCellStyle(contextStyle);  //  设备名称
            cell = row.createCell(1);cell.setCellValue("根");cell.setCellStyle(contextStyle);            //  单位
            cell = row.createCell(2);cell.setCellValue(iPointList11.size());cell.setCellStyle(contextStyle);               //  数量

            cell = row.createCell(3);cell.setCellValue("");cell.setCellStyle(contextStyle);	            //	生产厂家
            cell = row.createCell(4);cell.setCellValue("");cell.setCellStyle(contextStyle);//  设备型号
            cell = row.createCell(5);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂年月
            cell = row.createCell(6);cell.setCellValue("");cell.setCellStyle(contextStyle);//  投运日期
            cell = row.createCell(7);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维部门
            cell = row.createCell(8);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维保管组
            cell = row.createCell(9);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂编号
            cell = row.createCell(10);cell.setCellValue("");cell.setCellStyle(contextStyle);//  备注
        }

        //  导线380,220
        /*IPoint iPoint44 = new IPoint();
        iPoint44.setByqId(id);
        iPoint44.setVoltage("400v");
        List<IPoint> iPointList33 = iPointService.selectIPointList(iPoint44);
        float aw11 = 0 , aw21 = 0 , sum11 = 0;
        float aw12 = 0 , aw22 = 0 , sum12 = 0;

        for (IPoint iPoint : iPointList33) {
            if(iPoint.getVoltage() != null && iPoint.getVoltage().equals("400v")){
                if(iPoint.getSpan() != null)
                    aw11 += iPoint.getSpan().floatValue();
                else if(iPoint.getInletLine() != null)
                    aw21 += iPoint.getInletLine().floatValue();
            }else if(iPoint.getVoltage() != null && iPoint.getVoltage().equals("220v")){
                if(iPoint.getSpan() != null)
                    aw12 += iPoint.getSpan().floatValue();
                else if(iPoint.getInletLine() != null)
                    aw22 += iPoint.getInletLine().floatValue();
            }

        }
        sum11 = aw11 + aw21;
        sum12 = aw12 + aw22;
        System.err.println("sum11=="+sum11);
        System.err.println("sum12=="+sum12);*/

        // 400v线路长度
//        Float il400v = iPointService.totalLine(id,"400v");
//        // 220v线路长度
//        Float il220v = iPointService.totalLine(id,"220v");
//
//        if(il400v == null)il400v = 0F ;
//        if(il220v == null)il220v = 0F ;

        IPoint iPoint_220v = new IPoint();
        iPoint_220v.setByqId(id);
        Float line_length_380v = 0f;    //  380v线路长度
        Float line_380v_1 = 0f;    //  380v线路长度
        Float line_380v_2 = 0f;    //  380v线路长度

        Float line_length_220v = 0f;    //  220v线路长度
        Float line_220v_1 = 0f;    //  220v线路长度
        Float line_220v_2 = 0f;    //  220v线路长度

        List<IPoint> iPointList_220v = iPointService.selectIPointList(iPoint_220v);
        if(iPointList_220v !=null){
            for (IPoint iPoint : iPointList_220v) {
                if (iPoint.getVoltage()!=null){
                    if (iPoint.getVoltage().equals("400v")){
                        if(iPoint.getType().equals("表箱") && iPoint.getInletLine() != null){
                            line_380v_1 = line_380v_1 + iPoint.getInletLine().floatValue();
                        }else if(iPoint.getSpan() != null){   //  档距不为空且点位类型不为表箱时（表箱的进户线长度不计算在总的导线长度，需要单独列出）
                            line_380v_2 = iPoint.getSpan().floatValue()+line_380v_2;
                        }
                        line_length_380v = line_380v_1 + line_380v_2;
                    }
                    if (iPoint.getVoltage().equals("220v")){
                        if(iPoint.getType().equals("表箱") && iPoint.getInletLine() != null){
                            line_220v_1 = iPoint.getInletLine().floatValue()+line_220v_1;
                        }else if(iPoint.getSpan() != null){   //  档距不为空且点位类型不为表箱时（表箱的进户线长度不计算在总的导线长度，需要单独列出）
                            line_220v_2 = iPoint.getSpan().floatValue()+line_220v_2;
                        }
                        line_length_220v = line_220v_1 + line_220v_2;
                    }
                }
            }
        }


        for (int i = 0; i < 2; i++) {
            if(i==0){
                if(bsc == 0){
                    row=sheet.createRow(i+8+asd);
                }else{
                    row=sheet.createRow(bsc+9+asd);
                }
                cell = row.createCell(0);cell.setCellValue("导线380");cell.setCellStyle(contextStyle);  //  设备名称
                cell = row.createCell(1);cell.setCellValue("千米");cell.setCellStyle(contextStyle);            //  单位
                cell = row.createCell(2);cell.setCellValue(div(line_length_380v,1000,2));cell.setCellStyle(contextStyle);               //  数量
            }else{
                if(bsc == 0){
                    row=sheet.createRow(i+8+asd);
                }else{
                    row=sheet.createRow(i+9+asd+bsc);
                }
                cell = row.createCell(0);cell.setCellValue("导线220");cell.setCellStyle(contextStyle);  //  设备名称
                cell = row.createCell(1);cell.setCellValue("千米");cell.setCellStyle(contextStyle);            //  单位
                cell = row.createCell(2);cell.setCellValue(div(line_length_220v,1000,2));cell.setCellStyle(contextStyle);               //  数量
            }
            row.setHeightInPoints(25);  // 行高
            cell = row.createCell(3);cell.setCellValue("");cell.setCellStyle(contextStyle);	            //	生产厂家
            cell = row.createCell(4);cell.setCellValue("");cell.setCellStyle(contextStyle);//  设备型号
            cell = row.createCell(5);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂年月
            cell = row.createCell(6);cell.setCellValue("");cell.setCellStyle(contextStyle);//  投运日期
            cell = row.createCell(7);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维部门
            cell = row.createCell(8);cell.setCellValue("");cell.setCellStyle(contextStyle);//  运维保管组
            cell = row.createCell(9);cell.setCellValue("");cell.setCellStyle(contextStyle);//  出厂编号
            cell = row.createCell(10);cell.setCellValue("");cell.setCellStyle(contextStyle);//  备注
        }


        //  线路
        ILine iLine1 = new ILine();
        iLine1.setByqId(id);
        List<ILine> iLineList = iLineService.selectILineList(iLine1);
        IPoint iPoint = new IPoint();
        iPoint.setByqId(id);
//        iPoint.setLineId(iLineList.get(i).getId());
        List<IPoint> iPointList12 = new ArrayList<>();

        for (ILine iLine : iLineList) {
            iPoint.setLineId(iLine.getId());
            iPointList12 = iPointService.selectIPointList(iPoint);
            Double oneNum = 0d , twoNum = 0d ;
            List<IMaterial> iMaterials = new ArrayList<>();
            String newMater = "";
            StringBuffer sbf = new StringBuffer();
            List<String> list1 = new ArrayList();
            for (IPoint point : iPointList12) {
                if(point.getSpan() != null){
                    oneNum += point.getSpan().doubleValue();
                }else if(point.getInletLine() != null){
                    twoNum += point.getInletLine().doubleValue();
                }
                IMaterial iMaterial = new IMaterial();
                iMaterial.setByqId(id);
                iMaterial.setPointId(point.getId());
                iMaterials = iMaterialService.selectIMaterialList(iMaterial);


                if(iMaterials.size()!=0){
                    for (IMaterial material : iMaterials) {
                        if(material.getBigType().equals("架空绝缘导线") || material.getBigType().equals("钢芯铝绞线") ||
                                material.getBigType().equals("0.4kV电缆") || material.getBigType().equals("交联电缆")){
                            list1.add(material.getName());
                        }
                    }

                }

            }

            List<String> uts = removeDuplicate(list1);
            for (String ut : uts) {
                sbf.append(ut);
                sbf.append(";");
            }
            newMater = sbf.substring(0,sbf.lastIndexOf(";"));

            iLine.setNewMater(newMater);
            iLine.setSunTotal(new BigDecimal(oneNum+twoNum));
        }

        for (int i = 0; i < iLineList.size(); i++) {

            if(bsc == 0){
                row=sheet.createRow(i+10+asd);
            }else{
                row=sheet.createRow(i+11+asd+bsc);
            }

            row.setHeightInPoints(25);  // 行高

            cell=row.createCell(0);cell.setCellValue(iLineList.get(i).getName());cell.setCellStyle(contextStyle);	//	设备名称
            cell=row.createCell(1);cell.setCellValue("千米");cell.setCellStyle(contextStyle);     //  单位

            cell=row.createCell(2);//3 row 2 column
            if(iLineList.get(i).getSunTotal().doubleValue() ==0)
                cell.setCellValue("");	//	数量
            else
                cell.setCellValue(div(iLineList.get(i).getSunTotal().doubleValue(),1000 ,2));	//	数量
            cell.setCellStyle(contextStyle);

            cell=row.createCell(3);cell.setCellValue("");cell.setCellStyle(contextStyle);//	生产厂家

            cell=row.createCell(4);//3 row 4 column
            if (iLineList.get(i).getNewMater() == null)
                cell.setCellValue("");	//	设备型号
            else
                cell.setCellValue(iLineList.get(i).getNewMater());	//	设备型号
            cell.setCellStyle(contextStyle);

            cell=row.createCell(5);cell.setCellValue("");cell.setCellStyle(contextStyle);//	出厂年月
            cell=row.createCell(6);cell.setCellValue("");cell.setCellStyle(contextStyle);//	投运日期
            cell=row.createCell(7);cell.setCellValue("");cell.setCellStyle(contextStyle);//	运维部门
            cell=row.createCell(8); cell.setCellValue("");cell.setCellStyle(contextStyle);//	运维班组
            cell=row.createCell(9);cell.setCellValue("");cell.setCellStyle(contextStyle);//	出厂编号
            cell=row.createCell(10);cell.setCellValue("");cell.setCellStyle(contextStyle);//	备注


        }

    }


    public void getGTdoc(HSSFWorkbook workbook , HSSFRow row , HSSFSheet sheet , String id){

        HSSFCellStyle contextStyle = genContextStyle(workbook);
        HSSFCell cell ;

        IPoint iPoint = new IPoint();
        iPoint.setByqId(id);
        List<IPoint> iPointList = iPointService.selectIPointList(iPoint);
        List<IPoint> iPointList22 = new ArrayList<>();

        for (IPoint point : iPointList) {
            if(point.getType().equals("杆塔") || point.getType().equals("同杆架设")){
                iPointList22.add(point);
            }
        }
//        int pole_8 = 0 , pole_10 = 0 , pole_12 = 0 , pole_15 = 0 , pole_18 = 0;
        for (int i = 0; i < iPointList22.size(); i++) {

            ILine iLine = iLineService.selectILineById(iPointList22.get(i).getLineId());
            IMaterial iMaterial = new IMaterial();
            iMaterial.setPointId(iPointList22.get(i).getId());
            List<IMaterial> iMaterialList = iMaterialService.selectIMaterialList(iMaterial);
            List<IMaterial> iMaterialList11 = new ArrayList<>();

            for (IMaterial material : iMaterialList) {
                if(material.getBigType().equals("架空绝缘导线") || material.getBigType().equals("钢芯铝绞线") ||
                        material.getBigType().equals("0.4kV电缆") || material.getBigType().equals("交联电缆")){
                    iMaterialList11.add(material);
                }
            }

            List<IMaterial> iMaterialList22 = new ArrayList<>();
            List<IMaterial> iMaterialList33 = new ArrayList<>();
            IPole iPole = new IPole();
            iPole.setPId(iPointList22.get(i).getId());
            List<IPole> iPoleList = iPoleService.selectIPoleList(iPole);
            if(iPoleList.size() == 0){
                iPole.setPId(iPointList22.get(i).getPoleId());
                iPoleList = iPoleService.selectIPoleList(iPole);
            }

            row = sheet.createRow(i+3);
            row.setHeightInPoints(25);  // 行高
            cell = row.createCell(0);cell.setCellValue(iLine.getName());cell.setCellStyle(contextStyle);                //  所属线路
            cell = row.createCell(1);cell.setCellValue(iPointList22.get(i).getCode());cell.setCellStyle(contextStyle);  //  杆号
            cell = row.createCell(2);cell.setCellValue(iPointList22.get(i).getVoltage());cell.setCellStyle(contextStyle);  //  电压等级
            cell = row.createCell(3);cell.setCellValue(iPoleList.get(0).getType());cell.setCellStyle(contextStyle);  //  杆型

            cell = row.createCell(4);
            if(iPointList22.get(i).getPoleHeight() != null && iPointList22.get(i).getPoleHeight().equals("8米")){
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(5);
            if(iPointList22.get(i).getPoleHeight() != null &&
                    iPointList22.get(i).getPoleHeight().equals("10米")){
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(6);
            if(iPointList22.get(i).getPoleHeight() != null &&
                    (iPointList22.get(i).getPoleHeight().equals("12米") ||
                            iPointList22.get(i).getPoleHeight().equals("12米特型"))){
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(7);
            if(iPointList22.get(i).getPoleHeight() != null &&
                    (iPointList22.get(i).getPoleHeight().equals("15米") ||
                            iPointList22.get(i).getPoleHeight().equals("15米特型"))){
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(8);
            if(iPointList22.get(i).getPoleHeight() != null &&
                    (iPointList22.get(i).getPoleHeight().equals("18米") ||
                            iPointList22.get(i).getPoleHeight().equals("18米特型"))){
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            //  型号
            String newName = "";
            if(iMaterialList11.size()>0){
                StringBuffer materName = new StringBuffer();
                for (IMaterial material : iMaterialList11) {
                    materName.append(material.getName());
                    materName.append(";");
                }
                //  取消末尾分号
                newName = materName.substring(0,materName.lastIndexOf(";"));
            }

            cell = row.createCell(9);cell.setCellValue(newName);cell.setCellStyle(contextStyle);  //  导线型号

            cell = row.createCell(10);cell.setCellValue(iPointList22.get(i).getSpan().doubleValue());cell.setCellStyle(contextStyle);  //  档距

            for (IMaterial material : iMaterialList) {
                if(material.getBigType().equals("横担")){
                    iMaterialList22.add(material);
                }
            }

            cell = row.createCell(11);
            if(iMaterialList22.size() != 0){
                for (IMaterial material : iMaterialList22) {
                    if( material.getBigType().equals("横担") && material.getLable().equals("1500")){   //`横担
                        cell.setCellValue(material.getCount().doubleValue());
                    }
                }
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(12);
            if(iMaterialList22.size() != 0){
                for (IMaterial material : iMaterialList22) {
                    if( material.getBigType().equals("横担") && material.getLable().equals("1630")){   //`横担
                        cell.setCellValue(material.getCount().doubleValue());
                    }
                }
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(13);
            if(iMaterialList22.size() != 0){
                for (IMaterial material : iMaterialList22) {
                    if( material.getBigType().equals("横担") && material.getLable().equals("1750")){   //`横担
                        cell.setCellValue(material.getCount().doubleValue());
                    }
                }
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(14);
            if(iMaterialList22.size() != 0){
                for (IMaterial material : iMaterialList22) {
                    if( material.getBigType().equals("横担") && material.getLable().equals("1800")){   //`横担
                        cell.setCellValue(material.getCount().doubleValue());
                    }
                }
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            /*cell = row.createCell(11);
            if( iMaterialList22.size() != 0 && iMaterialList22.get(0).getBigType().equals("横担") && iMaterialList22.get(0).getLable().equals("1500")){   //`横担
                cell.setCellValue(iMaterialList22.get(0).getCount().doubleValue());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(12);
            if(iMaterialList22.size() != 0 && iMaterialList22.get(0).getBigType().equals("横担") && iMaterialList22.get(0).getLable().equals("1630")){
                cell.setCellValue(iMaterialList22.get(0).getCount().doubleValue());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(13);
            if(iMaterialList22.size() != 0 && iMaterialList22.get(0).getBigType().equals("横担") && iMaterialList22.get(0).getLable().equals("1750")){
                cell.setCellValue(iMaterialList22.get(0).getCount().doubleValue());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(14);
            if(iMaterialList22.size() != 0 && iMaterialList22.get(0).getBigType().equals("横担") && iMaterialList22.get(0).getLable().equals("1800")){
                cell.setCellValue(iMaterialList22.get(0).getCount().doubleValue());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);*/

            cell = row.createCell(15);
            if(iPointList22.get(i).getPullNum() != null && iPointList22.get(i).getPullNum()>0){   //  拉线数量
                cell.setCellValue(iPointList22.get(i).getPullNum());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            for (IMaterial material : iMaterialList) {
                if(material.getBigType().equals("绝缘子")){
                    iMaterialList33.add(material);
                }
            }

            cell = row.createCell(16);
            if(iMaterialList33.size()!=0){
                for (IMaterial material : iMaterialList33) {
                    if(material.getBigType().equals("绝缘子") && material.getMidType().equals("针式")){
                        cell.setCellValue(material.getCount().doubleValue());
                    }
                }
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(17);
            if(iMaterialList33.size()!=0){
                for (IMaterial material : iMaterialList33) {
                    if(material.getBigType().equals("绝缘子") && material.getMidType().equals("柱式")){
                        cell.setCellValue(material.getCount().doubleValue());
                    }
                }
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(18);
            if(iMaterialList33.size()!=0){
                for (IMaterial material : iMaterialList33) {
                    if(material.getBigType().equals("绝缘子") && material.getMidType().equals("碟式")){
                        cell.setCellValue(material.getCount().doubleValue());
                    }
                }
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(19);
            if(iMaterialList33.size()!=0){
                for (IMaterial material : iMaterialList33) {
                    if(material.getBigType().equals("绝缘子") && material.getMidType().equals("悬式")){
                        cell.setCellValue(material.getCount().doubleValue());
                    }
                }
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            
            
            
            /*if(iMaterialList33.size() != 0 && iMaterialList33.get(0).getBigType().equals("绝缘子") && iMaterialList33.get(0).getMidType().equals("针式")){   //绝缘子
                cell.setCellValue(iMaterialList33.get(0).getCount().doubleValue());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(17);
            if(iMaterialList33.size() != 0 && iMaterialList33.get(0).getBigType().equals("绝缘子") && iMaterialList33.get(0).getMidType().equals("柱式")){
                cell.setCellValue(iMaterialList33.get(0).getCount().doubleValue());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(18);
            if(iMaterialList33.size() != 0 && iMaterialList33.get(0).getBigType().equals("绝缘子") && iMaterialList33.get(0).getMidType().equals("碟式")){
                cell.setCellValue(iMaterialList33.get(0).getCount().doubleValue());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(19);
            if(iMaterialList33.size() != 0 && iMaterialList33.get(0).getBigType().equals("绝缘子") && iMaterialList33.get(0).getMidType().equals("悬式")){
                cell.setCellValue(iMaterialList33.get(0).getCount().doubleValue());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);*/

        }






    }

    public void getBXdoc(HSSFWorkbook workbook , HSSFRow row , HSSFSheet sheet , String id){

        IBoxes iBoxes = new IBoxes();
        iBoxes.setByqId(id);
        List<IBoxes> iBoxesList = iBoxesService.getBoxesForByqId(iBoxes);

        HSSFCellStyle contextStyle = genContextStyle(workbook);
        HSSFCell cell ;

        //  线路、电压等级
        for (IBoxes boxes : iBoxesList) {
            IPoint iPoint = iPointService.selectIPointById(boxes.getPId());
            ILine iLine = iLineService.selectILineById(iPoint.getLineId());
            boxes.setLineId(iLine.getName());
            if(iPoint.getVoltage().equals("400v")){
                boxes.setRunCode("380v");
            }else if(iPoint.getVoltage().equals("220v")){
                boxes.setRunCode(iPoint.getVoltage());
            }
        }

        int dxs = 0, sxs = 0;
        int bw_one=0 , bw_two=0 , bw_four=0 , bw_six=0 , bw_nine=0 , bw_twelve = 0;
        for (int i = 0; i<iBoxesList.size();i++) {
            row = sheet.createRow(i+3);
            row.setHeightInPoints(25);  // 行高

            IPoint iPoint = iPointService.selectIPointById(iBoxesList.get(i).getPId());

            cell = row.createCell(0);cell.setCellValue(iBoxesList.get(i).getLineId());cell.setCellStyle(contextStyle);  //  所属线路
            cell = row.createCell(1);cell.setCellValue(iPoint.getCode());cell.setCellStyle(contextStyle);  //  表箱号
            cell = row.createCell(2);cell.setCellValue(iBoxesList.get(i).getRunCode());cell.setCellStyle(contextStyle);  //  电压等级
            cell = row.createCell(3);
            if(iBoxesList.get(i).getNum() != null && iBoxesList.get(i).getNum().equals("单相")){
//                cell.setCellValue(++dxs);
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);  //  单相

            cell = row.createCell(4);
            if(iBoxesList.get(i).getNum() != null && iBoxesList.get(i).getNum().equals("三相")){
//                cell.setCellValue(++sxs);
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);  //  三相

            cell = row.createCell(5);
            if(iBoxesList.get(i).getWs() != null && iBoxesList.get(i).getWs().equals("1表位")){
//                cell.setCellValue(++bw_one);
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);  //

            cell = row.createCell(6);
            if(iBoxesList.get(i).getWs() != null && iBoxesList.get(i).getWs().equals("2表位")){
//                cell.setCellValue(++bw_two);
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);  //

            cell = row.createCell(7);
            if(iBoxesList.get(i).getWs() != null && iBoxesList.get(i).getWs().equals("4表位")){
//                cell.setCellValue(++bw_four);
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);  //

            cell = row.createCell(8);
            if(iBoxesList.get(i).getWs() != null && iBoxesList.get(i).getWs().equals("6表位")){
//                cell.setCellValue(++bw_six);
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);  //

            cell = row.createCell(9);
            if(iBoxesList.get(i).getWs() != null && iBoxesList.get(i).getWs().equals("9表位")){
//                cell.setCellValue(++bw_nine);
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);  //

            cell = row.createCell(10);
            if(iBoxesList.get(i).getWs() != null && iBoxesList.get(i).getWs().equals("12表位")){
//                cell.setCellValue(++bw_twelve);
                cell.setCellValue(1);
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);  //

            cell = row.createCell(11);cell.setCellValue(iBoxesList.get(i).getInstallCount());cell.setCellStyle(contextStyle);  //  所属线路
        }

    }

    public void getBJdoc(HSSFWorkbook workbook , HSSFRow row , HSSFSheet sheet , String id){

        HSSFCellStyle contextStyle = genContextStyle(workbook);
        HSSFCell cell ;

        /*KBj kBj = new KBj();
        kBj.setByqId(id);
        List<KBj> kBjList = kBjService.selectKBjList(kBj);
        for (int i = 0; i < kBjList.size(); i++) {
            row = sheet.createRow(i+3);
            row.setHeightInPoints(25);  // 行高

            IPoint iPoint = iPointService.selectIPointById(kBjList.get(i).getPId());
            IDevice iDevice = iDeviceService.selectIDeviceById(kBjList.get(i).getBoxId());

            cell = row.createCell(0);cell.setCellValue(iPoint.getCode());cell.setCellStyle(contextStyle);     //  表箱号
            cell = row.createCell(1);
            if(kBjList.get(i).getCode() != null ){
                cell.setCellValue(kBjList.get(i).getCode());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);     //  户号

            cell = row.createCell(2);
            if(kBjList.get(i).getName() !=null ){
                cell.setCellValue(kBjList.get(i).getName());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);     //  户名

            cell = row.createCell(3);
            if(kBjList.get(i).getBCode() !=null ){
                cell.setCellValue(kBjList.get(i).getBCode());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);     //  表号

            if(iDevice != null && iDevice.getName().indexOf("单相") != -1){
//                cell = row.createCell(4);cell.setCellValue(++dx);cell.setCellStyle(contextStyle);     //  单相
                cell = row.createCell(4);cell.setCellValue(1);cell.setCellStyle(contextStyle);     //  单相
            }else{
                cell = row.createCell(4);cell.setCellValue("");cell.setCellStyle(contextStyle);     //  单相
            }

            if(iDevice != null && iDevice.getName().indexOf("三相") != -1){
//                cell = row.createCell(5);cell.setCellValue(++sx);cell.setCellStyle(contextStyle);     //  三相
                cell = row.createCell(5);cell.setCellValue(1);cell.setCellStyle(contextStyle);     //  三相
            }else{
                cell = row.createCell(5);cell.setCellValue("");cell.setCellStyle(contextStyle);     //  三相
            }

            if(kBjList.get(i).getType() != null && kBjList.get(i).getType().equals("用户表")){
//                cell = row.createCell(5);cell.setCellValue(++sx);cell.setCellStyle(contextStyle);     //  用户表
                cell = row.createCell(6);cell.setCellValue(1);cell.setCellStyle(contextStyle);     //  用户表
            }else{
                cell = row.createCell(6);cell.setCellValue("");cell.setCellStyle(contextStyle);     //  用户表
            }

            if(kBjList.get(i).getType() != null && kBjList.get(i).getType().equals("关口表")){
//                cell = row.createCell(5);cell.setCellValue(++sx);cell.setCellStyle(contextStyle);     //  关口表
                cell = row.createCell(7);cell.setCellValue(1);cell.setCellStyle(contextStyle);     //  关口表
            }else{
                cell = row.createCell(7);cell.setCellValue("");cell.setCellStyle(contextStyle);     //  关口表
            }

//                cell = row.createCell(5);cell.setCellValue(++sx);cell.setCellStyle(contextStyle);     //  规格型号
            if(iDevice != null){
                cell = row.createCell(8);cell.setCellValue(iDevice.getName());cell.setCellStyle(contextStyle);     //  规格型号
            }else{
                cell = row.createCell(8);cell.setCellValue("");cell.setCellStyle(contextStyle);     //  规格型号
            }
        }*/
        
        IDevice iDevice = new IDevice();
        iDevice.setByqId(id);
        List<IDevice> iDeviceList = iDeviceService.getBigType(iDevice);
        List<IDevice> iDeviceList22 = new ArrayList<>();
        for (IDevice device : iDeviceList) {
            if(device.getBigType().equals("电能表")){
                iDeviceList22.add(device);
            }
        }
//        int dx = 0 , sx = 0;
        for (int i = 0; i < iDeviceList22.size(); i++) {
            row = sheet.createRow(i+3);
            row.setHeightInPoints(25);  // 行高

            IPoint iPoint = iPointService.selectIPointById(iDeviceList22.get(i).getPointId());

            KBj kBj = new KBj();
            kBj.setByqId(iDeviceList22.get(i).getByqId());
//            kBj.setPId(iDeviceList22.get(i).getPointId());
            kBj.setBoxId(iDeviceList22.get(i).getId());
            List<KBj> kBjList = kBjService.selectKBjList(kBj);

            cell = row.createCell(0);cell.setCellValue(iPoint.getCode());cell.setCellStyle(contextStyle);     //  表箱号
            cell = row.createCell(1);
            if(kBjList.size() !=0 ){
                cell.setCellValue(kBjList.get(0).getCode());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);     //  户号

            cell = row.createCell(2);
            if(kBjList.size() !=0 ){
                cell.setCellValue(kBjList.get(0).getName());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);     //  户名

            cell = row.createCell(3);
            if(kBjList.size() !=0 ){
                cell.setCellValue(kBjList.get(0).getBCode());
            }else{
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);     //  表号

            if(iDeviceList22.get(i).getName().indexOf("单相") != -1){
//                cell = row.createCell(4);cell.setCellValue(++dx);cell.setCellStyle(contextStyle);     //  单相
                cell = row.createCell(4);cell.setCellValue(1);cell.setCellStyle(contextStyle);     //  单相
            }else{
                cell = row.createCell(4);cell.setCellValue("");cell.setCellStyle(contextStyle);     //  单相
            }

            if(iDeviceList22.get(i).getName().indexOf("三相") != -1){
//                cell = row.createCell(5);cell.setCellValue(++sx);cell.setCellStyle(contextStyle);     //  三相
                cell = row.createCell(5);cell.setCellValue(1);cell.setCellStyle(contextStyle);     //  三相
            }else{
                cell = row.createCell(5);cell.setCellValue("");cell.setCellStyle(contextStyle);     //  三相
            }

            //  用户表
            if(kBjList.size()!=0 && kBjList.get(0).getType()!=null && kBjList.get(0).getType().equals("用户表")){
//                cell = row.createCell(5);cell.setCellValue(++sx);cell.setCellStyle(contextStyle);     //
                cell = row.createCell(6);cell.setCellValue(1);cell.setCellStyle(contextStyle);     //
            }else{
                cell = row.createCell(6);cell.setCellValue("");cell.setCellStyle(contextStyle);     //
            }

            //  关口表
            if(kBjList.size()!=0 && kBjList.get(0).getType()!=null && kBjList.get(0).getType().equals("关口表")){
//                cell = row.createCell(5);cell.setCellValue(++sx);cell.setCellStyle(contextStyle);     //
                cell = row.createCell(7);cell.setCellValue(1);cell.setCellStyle(contextStyle);     //
            }else{
                cell = row.createCell(7);cell.setCellValue("");cell.setCellStyle(contextStyle);     //
            }

            //  型号
            if(iDeviceList22.get(i).getName() != null){
//                cell = row.createCell(5);cell.setCellValue(++sx);cell.setCellStyle(contextStyle);     //
                cell = row.createCell(8);cell.setCellValue(iDeviceList22.get(i).getName());cell.setCellStyle(contextStyle);     //
            }else{
                cell = row.createCell(8);cell.setCellValue("");cell.setCellStyle(contextStyle);     //
            }

        }

    }




    /**
     *  导出照片
     */
    @RequestMapping("/photo")
    public void exportsimg(String id , HttpServletRequest request, HttpServletResponse response) throws IOException, DocumentException {
        BByq bByq = bByqService.selectBByqById(id);

        response.setContentType("application/msword;charset=utf-8");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(((bByq.getName()==null?"":bByq.getName()) + "照片.doc").getBytes(), "iso-8859-1"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        Document document = new Document();
        BaseFont bfChinese = BaseFont.createFont("C:/WINDOWS/Fonts/SIMSUN.TTC,1", BaseFont.IDENTITY_H, BaseFont.EMBEDDED);

        RtfWriter2.getInstance(document, response.getOutputStream());
        com.lowagie.text.Font titleFontye = new com.lowagie.text.Font(bfChinese,8, com.lowagie.text.Font.BOLD);
        com.lowagie.text.HeaderFooter footer = new com.lowagie.text.HeaderFooter(new Phrase("页码：",titleFontye), true);
        footer.setAlignment(1);
        footer.setBorder(Rectangle.NO_BORDER);

        document.setFooter(footer);
        document.open();
        setexportsimg(document,bfChinese,bByq);

        document.close();


    }
    public void setexportsimg(Document document  ,BaseFont bfChinese ,BByq bByq ) throws DocumentException, IOException {

        //表题字体
        com.lowagie.text.Font titleFont = new com.lowagie.text.Font(bfChinese,11, com.lowagie.text.Font.BOLD);
        //表格字体
        com.lowagie.text.Font contextFont = new com.lowagie.text.Font(bfChinese, 8, com.lowagie.text.Font.NORMAL);
        // 签字字体
        com.lowagie.text.Font qinFont = new com.lowagie.text.Font(bfChinese, 10, com.lowagie.text.Font.NORMAL);
        Paragraph title = new Paragraph((bByq.getName()==null?"":bByq.getName()) + "照片");
        title.setAlignment(Element.ALIGN_CENTER);
        title.setFont(titleFont);
        document.add(title);

        IPoint iPoint = new IPoint();
        iPoint.setByqId(bByq.getId());
        List<IPoint> iPointList = iPointService.selectIPointList(iPoint);

        addpoleimg(document, bByq, contextFont, iPointList);

    }



    private void addpoleimg(Document document, BByq bByq, com.lowagie.text.Font contextFont, List<IPoint> iPointList) throws DocumentException, IOException {
        for(IPoint iPoint:iPointList) {

            IPicture iPicture = new IPicture();
            iPicture.setPId(iPoint.getId());
            List<IPicture> iPictureList = iPictureService.selectIPictureList(iPicture);

            ILine iLine = iLineService.selectILineById(iPoint.getLineId());

            document.setMargins(40F,40F,20F,73F);	//	文档边距
            if(iPictureList.size()>0) {

                com.lowagie.text.Table table = new com.lowagie.text.Table(6);
                int[] withs = {1, 2, 1, 2, 1, 2};
                table.setWidths(withs);
//                * 表格所占页面宽度 author:yyli Sep 15, 2010
                table.setWidth(90);

//                * 居中显示 author:yyli Sep 15, 2010
                table.setAlignment(Element.ALIGN_CENTER);
//                * 自动填满 author:yyli Sep 15, 2010
                table.setAutoFillEmptyCells(true);

                if(!iPoint.getType().equals("变压器"))
                    OutPutUtils.addcell(table, iLine.getName()+"-"+iPoint.getCode()+"("+iPoint.getType()+")" , 0, 6, true, contextFont);
                else
                    OutPutUtils.addcell(table, iPoint.getCode()+"("+iPoint.getType()+")" , 0, 6, true, contextFont);

                //	一个点位设定最多显示3张照片
                int chushu = iPictureList.size() / 3;
                int yushu = iPictureList.size() % 3;

                if (yushu > 0) {
                    chushu = chushu + 1;
                }

//                int chushu = 1;
                for (int i = 0; i < chushu; i++) {
                    boolean one = false;
                    boolean two = false;
                    boolean three = false;

                    if ((3 * i) < iPictureList.size()) {
                        one = true;
                    }
                    if (((3 * i) + 1) < iPictureList.size()) {
                        two = true;
                    }
                    if (((3 * i) + 2) < iPictureList.size()) {
                        three = true;
                    }
                    if (one) {
                        File file=new File(ROOT +   iPictureList.get(3 * i).getAddress());
                        if(file.exists())
                        {
                            OutPutUtils.addcellimg(table, ROOT +   iPictureList.get(3 * i).getAddress(), 8, 2, true, contextFont);
                        }
                        else
                        {
                            OutPutUtils.addcell(table, "没找到照片", 8, 2, true, contextFont);

                        }
                    } else {
                        OutPutUtils.addcell(table, "", 8, 2, true, contextFont);
                    }
                    if (two) {
                        File file=new File(ROOT +   iPictureList.get((3 * i)+1).getAddress());
                        if(file.exists())
                        {
                            OutPutUtils.addcellimg(table, ROOT +   iPictureList.get((3 * i)+1).getAddress(), 8, 2, true, contextFont);
                        }
                        else
                        {
                            OutPutUtils.addcell(table, "没找到照片", 8, 2, true, contextFont);

                        }
                    } else {
                        OutPutUtils.addcell(table, "", 8, 2, true, contextFont);
                    }
                    if (three) {
                        File file=new File(ROOT +   iPictureList.get((3 * i)+2).getAddress());
                        if(file.exists())
                        {
                            OutPutUtils.addcellimg(table, ROOT +   iPictureList.get((3 * i)+2).getAddress(), 8, 2, true, contextFont);
                        }
                        else
                        {
                            OutPutUtils.addcell(table, "没找到照片", 8, 2, true, contextFont);

                        }
                    } else {
                        OutPutUtils.addcell(table, "", 8, 2, true, contextFont);
                    }


                    OutPutUtils.addcell(table, "名称", 0, 0, true, contextFont);
                    if (one) {
                        OutPutUtils.addcell(table, iPictureList.get(3 * i).getType(), 0, 0, true, contextFont);
                    } else {
                        OutPutUtils.addcell(table, "", 0, 0, true, contextFont);
                    }
                    OutPutUtils.addcell(table, "名称", 0, 0, true, contextFont);
                    if (two) {
                        OutPutUtils.addcell(table, iPictureList.get((3 * i) + 1).getType(), 0, 0, true, contextFont);
                    } else {
                        OutPutUtils.addcell(table, "", 0, 0, true, contextFont);
                    }
                    OutPutUtils.addcell(table, "名称", 0, 0, true, contextFont);
                    if (three) {
                        OutPutUtils.addcell(table, iPictureList.get((3 * i) + 2).getType(), 0, 0, true, contextFont);
                    } else {
                        OutPutUtils.addcell(table, "", 0, 0, true, contextFont);
                    }

                    OutPutUtils.addcell(table, "来源", 0, 0, true, contextFont);
                    if (one) {
                        OutPutUtils.addcell(table, iPictureList.get(3 * i).getResour(), 0, 0, true, contextFont);
                    } else {
                        OutPutUtils.addcell(table, "", 0, 0, true, contextFont);
                    }
                    OutPutUtils.addcell(table, "来源", 0, 0, true, contextFont);
                    if (two) {
                        OutPutUtils.addcell(table, iPictureList.get((3 * i) + 1).getResour(), 0, 0, true, contextFont);
                    } else {
                        OutPutUtils.addcell(table, "", 0, 0, true, contextFont);
                    }
                    OutPutUtils.addcell(table, "来源", 0, 0, true, contextFont);
                    if (three) {
                        OutPutUtils.addcell(table, iPictureList.get((3 * i) + 2).getResour(), 0, 0, true, contextFont);
                    } else {
                        OutPutUtils.addcell(table, "", 0, 0, true, contextFont);
                    }

                    OutPutUtils.addcell(table, "时间", 0, 0, true, contextFont);
                    if (one) {
                        OutPutUtils.addcell(table, iPictureList.get(3 * i).getTime() == null ? "" : new SimpleDateFormat("yyyy-MM-dd").format(iPictureList.get(3 * i).getTime()), 0, 0, true, contextFont);
                    } else {
                        OutPutUtils.addcell(table, "", 0, 0, true, contextFont);
                    }
                    OutPutUtils.addcell(table, "时间", 0, 0, true, contextFont);
                    if (two) {
                        OutPutUtils.addcell(table, iPictureList.get((3 * i) + 1).getTime() == null ? "" : new SimpleDateFormat("yyyy-MM-dd").format(iPictureList.get((3 * i) + 1).getTime()), 0, 0, true, contextFont);
                    } else {
                        OutPutUtils.addcell(table, "", 0, 0, true, contextFont);
                    }
                    OutPutUtils.addcell(table, "时间", 0, 0, true, contextFont);
                    if (three) {
                        OutPutUtils.addcell(table, iPictureList.get((3 * i) + 2).getTime() == null ? "" : new SimpleDateFormat("yyyy-MM-dd").format(iPictureList.get((3 * i) + 2).getTime()), 0, 0, true, contextFont);
                    } else {
                        OutPutUtils.addcell(table, "", 0, 0, true, contextFont);
                    }

                }
//                Paragraph nuss = new Paragraph("");	//	表与表之间加空行（调整格式）
//                document.add(nuss);
                document.add(table);
            }
        }
    }





    /**
     *  台账数据组装
     *
     */
    @PostMapping("LineAssets")
    @ResponseBody
    public List exportLineAssets(@RequestParam String id ) throws IOException {

        //  表格字段：设备名称，单位，数量，设备型号，生产厂家，出厂年月，投运日期，运维部门，运维班组，出厂编号，备注
        List list = new ArrayList();
        //  变压器ID
        BByq bByq = bByqService.selectBByqById(id);
        list.add(bByq);
        ILine iLine = new ILine();
        iLine.setByqId(id);
        List<ILine> iLineList = iLineService.selectILineList(iLine);
        //  统计格式：10kv支线(没有支线则可以不填)，台区变压器，低压配电柜，集中器，低压配电线路
        for (ILine line : iLineList) {
            //  查询归档后的设备材料表
            IDevice iDevice = new IDevice();
            iDevice.setByqId(id);
//            iDevice.setLineId(line.getId());
            List<IDevice> iDeviceList = iDeviceService.selectIDeviceList(iDevice);
            if(iDeviceList != null){
                for (IDevice device : iDeviceList) {
                    if (device.getBigType().equals("低压配电柜/盘")
                            || device.getMidType().equals("集中器")
                            || device.getBigType().equals("低压电流互感器")
                            || device.getBigType().equals("分支箱")){
                        list.add(device);
                    }
                }
            }
            IMaterial iMaterial = new IMaterial();
            IMaterial iMaterial22 = new IMaterial();
            iMaterial.setByqId(id);
//            iMaterial.setLineId(line.getId());
            List<IMaterial> iMaterialList = iMaterialService.selectIMaterialList(iMaterial);
            for (int i = 0; i < iMaterialList.size(); i++) {
                if(i==0){
                    iMaterial22.setByqId(id);
                    iMaterial22.setLineId(iMaterialList.get(i).getLineId());
                }
            }
            List<IMaterial> iMaterialList11 = iMaterialService.getBigType(iMaterial22);
            StringBuffer sbf = new StringBuffer();
            for (IMaterial material : iMaterialList11) {
                if(material.getBigType().equals("架空绝缘导线") || material.getBigType().equals("钢芯铝绞线") ||
                        material.getBigType().equals("0.4kV电缆") || material.getBigType().equals("交联电缆")){
                    sbf.append(material.getTechParameter());
                    sbf.append("; ");
                }
            }
            String trem = sbf.substring(0,sbf.lastIndexOf(";"));
            line.setRunCode(trem);
            list.add(line);
        }



        return list;
    }



    /**
     *  资产数据组装
     *
     * @param id
     * @return
     * @throws IOException
     */
    @PostMapping("/Assets")
    @ResponseBody
    public List extportAssets(@RequestParam String id ) throws IOException {

        //  变压器ID -- 查询该变压器下的所有线路的资产

        User user = userService.selectUserById(getUserId());


        List<Map<String , Object>> list = new ArrayList();
        BByq bByq = new BByq();
        bByq.setId(id);
        List<BByq> byqList = bByqService.selectBByqList(bByq);
//        ILine iLines = new ILine();
//        iLines.setByqId(id);
//        List<ILine> ilist = iLineService.selectILineList(iLines);

        IPoint iPoint = new IPoint();
        iPoint.setByqId(id);
        List<IPoint> iPointList = iPointService.selectIPointList(iPoint);

        //  以电缆或者架空线的数量循环
        IDevice iDevices = new IDevice();
        iDevices.setByqId(id);
        List<IDevice> iDeviceLists = iDeviceService.getBigType(iDevices);

        IMaterial iMaterial = new IMaterial();
        iMaterial.setByqId(id);
        List<IMaterial> iMaterialList22 = iMaterialService.getBigType(iMaterial);

        //  400v导线长度
//        Float i400v = iPointService.totalLine(id,"400v");
//
//        //  220v导线长度
//        Float i220v = iPointService.totalLine(id,"220v");
//
//        if(i400v == null)i400v = 0F ;
//        if(i220v == null)i220v = 0F ;
//
//        float i42 = i400v+i220v;



        IPoint iPoint_220v = new IPoint();
        iPoint_220v.setByqId(id);
        Float line_length_380v = 0f;    //  380v线路长度
        Float line_380v_1 = 0f;    //  380v线路长度
        Float line_380v_2 = 0f;    //  380v线路长度

        Float line_length_220v = 0f;    //  220v线路长度
        Float line_220v_1 = 0f;    //  220v线路长度
        Float line_220v_2 = 0f;    //  220v线路长度

        List<IPoint> iPointList_220v = iPointService.selectIPointList(iPoint_220v);
        if(iPointList_220v !=null){
            for (IPoint iPoints : iPointList_220v) {
                if (iPoints.getVoltage()!=null){
                    if (iPoints.getVoltage().equals("400v")){
                        if(iPoints.getType().equals("表箱") && iPoints.getInletLine() != null){
                            line_380v_1 = line_380v_1 + iPoints.getInletLine().floatValue();
                        }else if(iPoints.getSpan() != null){   //  档距不为空且点位类型不为表箱时（表箱的进户线长度不计算在总的导线长度，需要单独列出）
                            line_380v_2 = iPoints.getSpan().floatValue()+line_380v_2;
                        }
                        line_length_380v = line_380v_1 + line_380v_2;
                    }
                    if (iPoints.getVoltage().equals("220v")){
                        if(iPoints.getType().equals("表箱") && iPoints.getInletLine() != null){
                            line_220v_1 = iPoints.getInletLine().floatValue()+line_220v_1;
                        }else if(iPoints.getSpan() != null){   //  档距不为空且点位类型不为表箱时（表箱的进户线长度不计算在总的导线长度，需要单独列出）
                            line_220v_2 = iPoints.getSpan().floatValue()+line_220v_2;
                        }
                        line_length_220v = line_220v_1 + line_220v_2;
                    }
                }
            }
        }









        List<IMaterial> iMaterialList = new ArrayList<>();
        for (IMaterial material : iMaterialList22) {
            if(material.getBigType().equals("架空绝缘导线") || material.getBigType().equals("钢芯铝绞线") ||
                    material.getBigType().equals("0.4kV电缆") || material.getBigType().equals("交联电缆")){

                String[] iw = material.getPointId().split(",");
                Double saw = 0D;
                for (String s : iw) {

                    IPoint iPoint1 = iPointService.selectIPointById(s);
                    if(iPoint1 != null){
                        if(iPoint1.getType().equals("杆塔")){
                            saw = iPoint1.getSpan().doubleValue()+saw;
                        }else if(iPoint1.getType().equals("表箱")){
                            saw = iPoint1.getInletLine().doubleValue()+saw;
                        }else if(iPoint1.getType().equals("同杆架设")){
                            saw = iPoint1.getSpan().doubleValue()+saw;
                        }else if(iPoint1.getType().equals("下户点")){
                            saw = iPoint1.getSpan().doubleValue()+saw;
                        }else if(iPoint1.getType().equals("分支箱")){
                            saw = iPoint1.getSpan().doubleValue()+saw;
                        }
                    }
                }
                material.setNewCounts(saw);
                iMaterialList.add(material);

            }
        }

        int ios = 0;

        for (int i = 0; i < iMaterialList.size(); i++) {
            String lingModleJK = "";                    //  架空线规格型号
            Double lingJK = 0D;                         //  架空线长度
            String lingModleDL = "";                    //  电缆线规格型号
            Double lingDL = 0D;                         //  电缆线长度
            Double v380 = 0D;                           //  380v线路长度
            Double v220 = 0D;                           //  220v线路长度
            Double lineCount = 0D;                      //  线路长度合计

            Integer poleHeight8 = 0;                    //  8米杆高基数
            Integer poleHeight10 = 0;                   //  10米杆高基数
            Integer poleHeight12 = 0;                   //  12米杆高基数
            Integer poleHeight12_T = 0;                 //  12米特性杆杆高基数
            Integer poleHeight15 = 0;                   //  15米杆高基数
            Integer poleHeight15_T = 0;                   //  15米特性杆杆高基数
            Integer poleHeight18 = 0;                   //  18米杆高基数
            Integer poleHeight18_T = 0;                   //  18米特性杆杆高基数

            Integer sumTotal = 0;                       //  杆高总和
            String poleShape = "";                      //  杆塔形状
            String manufacturer = "";                   //  生产厂家
            String comDate = "";                        //  投运日期

            //  单相户表(智能型、普通型)、单相表箱、三相户表(智能型、普通型)、三相表箱
            Integer oneSurfaceSmart = 0;                //  单相户表--智能型数量
            Integer oneBox_one = 0;                     //  单相户表--1表位
            Integer oneBox_two = 0;                     //  单相户表--2表位
            Integer oneBox_four = 0;                    //  单相户表--4表位
            Integer oneBox_six = 0;                     //  单相户表--6表位
            Integer oneBox_nine = 0;                    //  单相户表--9表位
            Integer oneBox_twelve = 0;                  //  单相户表--12表位
            Integer oneBox_total = 0;                   //  单相户表--合计
            Integer threeSurfaceSmart = 0;              //  三相户表--智能型数量
            Integer threeBox = 0;                       //  三相表箱数量

            Integer concentrator = 0;                   //  集中器数量
            Integer mutualInductor = 0;                 //  互感器数量
            Integer lowBox = 0;                         //  低压电缆分支箱数量
            Integer lowCabinet = 0;                     //  成套低压配电柜数量

            Map<String, Object> objectMap = new HashMap<>();

            for (BByq byq : byqList) {
                Dept dept = deptService.selectDeptById(Long.parseLong(byq.getDeptId()));
                objectMap.put("area", byq.getName());
                objectMap.put("lineName", byq.getLine10kvName());
                objectMap.put("unitName",dept.getDeptName());
            }

            if (iMaterialList.get(i).getBigType().equals("架空绝缘导线")) {
                lingModleJK = iMaterialList.get(i).getName();
                lingJK += iMaterialList.get(i).getNewCounts();
            } else if (iMaterialList.get(i).getBigType().equals("钢芯铝绞线")) {
                lingModleJK = iMaterialList.get(i).getName();
                lingJK += iMaterialList.get(i).getNewCounts();
            } else if (iMaterialList.get(i).getBigType().equals("0.4kV电缆")) {
                lingModleDL = iMaterialList.get(i).getName();
                lingDL += iMaterialList.get(i).getNewCounts();
            } else if (iMaterialList.get(i).getBigType().equals("交联电缆")) {
                lingModleDL = iMaterialList.get(i).getName();
                lingDL += iMaterialList.get(i).getNewCounts();
            }

            for (IPoint point : iPointList) {
                if (point.getVoltage() != null && ios == 0) {

                    if (point.getVoltage().equals("400v") ) {
                        if(point.getType().equals("杆塔") || point.getType().equals("同杆架设"))
                            v380 += point.getSpan().doubleValue();
                        else if(point.getType().equals("表箱"))
                            v380 += point.getInletLine().doubleValue();
                    } else if (point.getVoltage().equals("220v")) {
                        if(point.getType().equals("杆塔") || point.getType().equals("同杆架设"))
                            v220 += point.getSpan().doubleValue();
                        else if(point.getType().equals("表箱"))
                            v220 += point.getInletLine().doubleValue();
                    }
                }

                IBoxes iBoxes = new IBoxes();
                iBoxes.setPId(point.getId());
                List<IBoxes> iBoxesList = iBoxesService.selectIBoxesList(iBoxes);
                for (IBoxes boxes : iBoxesList) {
                    if(boxes.getNum() != null){
                        if(boxes.getNum().equals("三相")){        //  三相表箱个数
                            threeBox = ++threeBox;
                        }/*else{
                            if(boxes.getWs().equals("1表位")) oneBox_one += boxes.getInstallCount();
                            else if(boxes.getWs().equals("2表位")) oneBox_two += boxes.getInstallCount();
                            else if(boxes.getWs().equals("4表位")) oneBox_four += boxes.getInstallCount();
                            else if(boxes.getWs().equals("6表位")) oneBox_six += boxes.getInstallCount();
                            else if(boxes.getWs().equals("9表位")) oneBox_nine += boxes.getInstallCount();
                            else if(boxes.getWs().equals("12表位")) oneBox_twelve += boxes.getInstallCount();
                        }*/
                    }
                }

            }

            oneBox_one = iBoxesService.getCountsForType(id,"单相","1表位");
            oneBox_two = iBoxesService.getCountsForType(id,"单相","2表位");
            oneBox_four = iBoxesService.getCountsForType(id,"单相","4表位");
            oneBox_six = iBoxesService.getCountsForType(id,"单相","6表位");
            oneBox_nine = iBoxesService.getCountsForType(id,"单相","9表位");
            oneBox_twelve = iBoxesService.getCountsForType(id,"单相","12表位");

            if(oneBox_one==null) oneBox_one=0;if(oneBox_two==null) oneBox_two=0;if(oneBox_four==null) oneBox_four=0;
            if(oneBox_six==null) oneBox_six=0;if(oneBox_nine==null) oneBox_nine=0;if(oneBox_twelve==null) oneBox_twelve=0;
            oneBox_total = oneBox_one + oneBox_two + oneBox_four + oneBox_six + oneBox_nine + oneBox_twelve;

            //  线路长度合计
            if (ios == 0) {
                lineCount = v380 + v220;
                if (ios == 0) {
                    for (IPoint point : iPointList) {
                        if (point.getType().equals("杆塔")) {
                            if (point.getPoleHeight().equals("8米")) {
                                poleHeight8 = ++poleHeight8;
                            } else if (point.getPoleHeight().equals("10米")) {
                                poleHeight10 = ++poleHeight10;
                            } else if (point.getPoleHeight().equals("12米")) {
                                poleHeight12 = ++poleHeight12;
                            } else if (point.getPoleHeight().equals("12米特型")) {
                                poleHeight12_T = ++poleHeight12_T;
                            } else if (point.getPoleHeight().equals("15米")) {
                                poleHeight15 = ++poleHeight15;
                            }else if (point.getPoleHeight().equals("15米特型")) {
                                poleHeight15_T = ++poleHeight15_T;
                            } else if (point.getPoleHeight().equals("18米")) {
                                poleHeight18 = ++poleHeight18;
                            } else if (point.getPoleHeight().equals("18米特型")) {
                                poleHeight18_T = ++poleHeight18_T;
                            }
                        }
                    }
                    sumTotal = poleHeight8 + poleHeight10 + poleHeight12 + poleHeight12_T + poleHeight15 + poleHeight15_T + poleHeight18 + poleHeight18_T;
                }

                //  杆基总和

                //  杆塔形状、生产厂家、投运日期

                //  单相户表(智能型、普通型)、单相表箱、三相户表(智能型、普通型)、三相表箱
                String iBoxesList23 = null ;
                String iBoxesList34 = null ;
                if (ios == 0) {

                    for (IDevice device : iDeviceLists) {

                        if (device.getBigType().equals("分支箱")) {
                            if (device.getName().indexOf("分支箱") != -1) {
                                lowBox = ++lowBox;
                            }
                        } else if (device.getBigType().equals("低压配电柜/盘")) {
                            if (device.getName().indexOf("低压配电柜") != -1) {
                                lowCabinet = ++lowCabinet;
                            }
                        } else if (device.getBigType().equals("采集通信设备")) {
                            if (device.getName().indexOf("集中器") != -1) {
                                concentrator = ++concentrator;
                            }
                        } /*else if (device.getBigType().equals("电能表")) {
                            if (device.getName().indexOf("单相") != -1) {
                                if (device.getName().indexOf("智能") != -1) {
                                    oneSurfaceSmart = ++oneSurfaceSmart;
                                }
                            } else if (device.getName().indexOf("三相") != -1) {
                                if (device.getName().indexOf("智能") != -1) {
                                    threeSurfaceSmart = ++threeSurfaceSmart;
                                }
                            }
                        } *//*else if (device.getBigType().equals("计量表箱")) {
                            if (device.getName().indexOf("单相") != -1) {
                                oneBox = ++oneBox;
                            } else if (device.getName().indexOf("三相") != -1) {
                                threeBox = ++threeBox;
                            }
                        }*/ else if (device.getBigType().equals("低压电流互感器")) {
                            if (device.getName().indexOf("低压电流互感器") != -1) {
                                mutualInductor = ++mutualInductor;
                            }
                        }
                    }

                    iBoxesList23 = iBoxesService.getCountsListForNum(id,"单相");
                    iBoxesList34 = iBoxesService.getCountsListForNum(id,"三相");

                }





                objectMap.put("lingModleJK", lingModleJK);
                if (lingJK != 0)
                    objectMap.put("lingJK", new BigDecimal(lingJK / 1000).setScale(2, BigDecimal.ROUND_HALF_UP));
                else objectMap.put("lingJK", "");
                objectMap.put("lingModleDL", lingModleDL);
                if (lingDL != 0)
                    objectMap.put("lingDL", new BigDecimal(lingDL / 1000).setScale(2, BigDecimal.ROUND_HALF_UP));
                else objectMap.put("lingDL", "");
//                if (v380 != 0) objectMap.put("v380", new BigDecimal(v380 / 1000).setScale(2, BigDecimal.ROUND_HALF_UP));
                if (v380 != 0) objectMap.put("v380", div(line_length_380v,1000,2));
                else objectMap.put("v380", "");
//                if (v220 != 0) objectMap.put("v220", new BigDecimal(v220 / 1000).setScale(2, BigDecimal.ROUND_HALF_UP));
                if (v220 != 0) objectMap.put("v220", div(line_length_220v,1000,2));
                else objectMap.put("v220", "");
                if (lineCount != 0)
//                    objectMap.put("lineCount", new BigDecimal(lineCount / 1000).setScale(2, BigDecimal.ROUND_HALF_UP));
                    objectMap.put("lineCount", div((line_length_380v+line_length_220v),1000,2));
                else objectMap.put("lineCount", "");

                if (poleHeight8 != 0) objectMap.put("poleHeight8", poleHeight8);else objectMap.put("poleHeight8", "");
                if (poleHeight10 != 0) objectMap.put("poleHeight10", poleHeight10);else objectMap.put("poleHeight10", "");
                if (poleHeight12 != 0) objectMap.put("poleHeight12", poleHeight12);else objectMap.put("poleHeight12", "");
                if (poleHeight12_T != 0) objectMap.put("poleHeight12_T", poleHeight12_T);else objectMap.put("poleHeight12_T", "");
                if (poleHeight15 != 0) objectMap.put("poleHeight15", poleHeight15);else objectMap.put("poleHeight15", "");
                if (poleHeight15_T != 0) objectMap.put("poleHeight15_T", poleHeight15_T);else objectMap.put("poleHeight15_T", "");
                if (poleHeight18 != 0) objectMap.put("poleHeight18", poleHeight18);else objectMap.put("poleHeight18", "");
                if (poleHeight18_T != 0) objectMap.put("poleHeight18_T", poleHeight18_T);else objectMap.put("poleHeight18_T", "");

                if (sumTotal != 0) objectMap.put("sumTotal", sumTotal);else objectMap.put("sumTotal", "");
                objectMap.put("poleShape", "");
                if (manufacturer != "") objectMap.put("manufacturer", "");else objectMap.put("manufacturer", "");
                if (comDate != "") objectMap.put("comDate", "");else objectMap.put("comDate", "");

                if (iBoxesList23 != null) objectMap.put("oneSurfaceSmart", iBoxesList23);else objectMap.put("oneSurfaceSmart", "");
                if (oneBox_one != 0 ) objectMap.put("oneBox_one", oneBox_one);else objectMap.put("oneBox_one", "");
                if (oneBox_two != 0) objectMap.put("oneBox_two", oneBox_two);else objectMap.put("oneBox_two", "");
                if (oneBox_four != 0) objectMap.put("oneBox_four", oneBox_four);else objectMap.put("oneBox_four", "");
                if (oneBox_six != 0) objectMap.put("oneBox_six", oneBox_six);else objectMap.put("oneBox_six", "");
                if (oneBox_nine != 0) objectMap.put("oneBox_nine", oneBox_nine);else objectMap.put("oneBox_nine", "");
                if (oneBox_twelve != 0) objectMap.put("oneBox_twelve", oneBox_twelve);else objectMap.put("oneBox_twelve", "");
                if (oneBox_total != 0) objectMap.put("oneBox_total", oneBox_total);else objectMap.put("oneBox_total", "");

                if (iBoxesList34 != null) objectMap.put("threeSurfaceSmart", iBoxesList34);else objectMap.put("threeSurfaceSmart", "");
                if (threeBox != 0) objectMap.put("threeBox", threeBox);else objectMap.put("threeBox", "");

                if (concentrator != 0) objectMap.put("concentrator", concentrator);else objectMap.put("concentrator", "");
                if (mutualInductor != 0) objectMap.put("mutualInductor", mutualInductor);else objectMap.put("mutualInductor", "");
                if (lowBox != 0) objectMap.put("lowBox", lowBox);else objectMap.put("lowBox", "");
                if (lowCabinet != 0) objectMap.put("lowCabinet", lowCabinet);else objectMap.put("lowCabinet", "");
                objectMap.put("remarks", "");

                list.add(objectMap);
                ++ios;
            }else {
                objectMap.put("lingModleJK", lingModleJK);
                if (lingJK != 0)
                    objectMap.put("lingJK", new BigDecimal(lingJK / 1000).setScale(2, BigDecimal.ROUND_HALF_UP));
                else objectMap.put("lingJK", "");
                objectMap.put("lingModleDL", lingModleDL);
                if (lingDL != 0)
                    objectMap.put("lingDL", new BigDecimal(lingDL / 1000).setScale(2, BigDecimal.ROUND_HALF_UP));
                else objectMap.put("lingDL", "");
                list.add(objectMap);
            }
        }
        return list;
    }


    private static List<String> removeDuplicate(List<String> list) {
        LinkedHashSet<String> set = new LinkedHashSet<String>(list.size());
        set.addAll(list);
        list.clear();
        list.addAll(set);
        return list;
    }


}
